Skip to content
The Chat Archive
gpt-3.5-turbo-0613· OpenAI WildChat-4.8M (AI2)

I want this code to use D column for F column and H for J and L for N and P for R and T…

  1. User

    I want this code to use D column for F column and H for J and L for N and P for R and T for V and X for Z: function generateIFSFunctionPRICES() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sourceSheet = ss.getSheetByName("Sheet2"); // Change "Sheet2" to the actual name of your sheet var targetSheet = ss.getSheetByName("Sheet1"); // Change "Sheet1" to the actual name of your sheet var lastRow = sourceSheet.getLastRow(); var items = sourceSheet.getRange("A2:A" + lastRow).getValues(); var prices = sourceSheet.getRange("B2:B" + lastRow).getValues();

    var columns = ["F","J", "N", "R", "V", "Z"]; // Columns where the IFS function will be written

    for (var i = 0; i < columns.length; i++) { var column = columns[i]; var functionString = "="+"IFNA("+"IFS(";

    for (var j = 0; j < items.length; j++) {
    

    var item = items[j][0]; var price = prices[j][0]; functionString += 'D2="' + item + '", ' + price;

    if (j !== items.length - 1) { functionString += ', '; } }

    functionString += ")"+","+""+")";
    targetSheet.getRange(column + "2:" + column ).setFormula(functionString);
    

    } }

  2. Assistant

    function generateIFSFunctionPRICES() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sourceSheet = ss.getSheetByName("Sheet2"); // Change "Sheet2" to the actual name of your sheet var targetSheet = ss.getSheetByName("Sheet1"); // Change "Sheet1" to the actual name of your sheet var lastRow = sourceSheet.getLastRow(); var items = sourceSheet.getRange("A2:A" + lastRow).getValues(); var prices = sourceSheet.getRange("B2:B" + lastRow).getValues();

    var columns = ["F","J", "N", "R", "V", "Z"]; // Columns where the IFS function will be written

    for (var i = 0; i < columns.length; i++) { var column = columns[i]; var functionString = "="+"IFNA("+"IFS(";

    for (var j = 0; j < items.length; j++) {
      var item = items[j][0];
      var price = prices[j][0];
      if (column === "F") {
        functionString += 'D2="' + item + '", ' + price;
      }
      else if (column === "J") {
        functionString += 'H2="' + item + '", ' + price;
      }
      else if (column === "N") {
        functionString += 'L2="' + item + '", ' + price;
      }
      else if (column === "R") {
        functionString += 'P2="' + item + '", ' + price;
      }
      else if (column === "V") {
        functionString += 'T2="' + item + '", ' + price;
      }
      else if (column === "Z") {
        functionString += 'X2="' + item + '", ' + price;
      }
      
      if (j !== items.length - 1) {
        functionString += ', ';
      }
    }
    
    functionString += ")," + "\"" + "\"" + ")";
    targetSheet.getRange(column + "2:" + column).setFormula(functionString);
    

    } }

  3. User

    do same with these columns function generateCOSTS() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sourceSheet = ss.getSheetByName("Sheet2"); // Change "Sheet2" to the actual name of your sheet var targetSheet = ss.getSheetByName("Sheet1"); // Change "Sheet1" to the actual name of your sheet var lastRow = sourceSheet.getLastRow(); var items = sourceSheet.getRange("A2:A" + lastRow).getValues(); var prices = sourceSheet.getRange("C2:C" + lastRow).getValues();

    var columns = ["G","K", "O", "S", "W", "AA"]; // Columns where the IFS function will be written

    for (var i = 0; i < columns.length; i++) { var column = columns[i]; var functionString = "="+"IFNA("+"IFS(";

    for (var j = 0; j < items.length; j++) {
    

    var item = items[j][0]; var price = prices[j][0]; functionString += 'D2="' + item + '", ' + price;

    if (j !== items.length - 1) { functionString += ', '; } }

    functionString += ")"+","+""+")";
    targetSheet.getRange(column + "2:" + column ).setFormula(functionString);
    

    } }

  4. Assistant

    function generateCOSTS() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sourceSheet = ss.getSheetByName("Sheet2"); // Change "Sheet2" to the actual name of your sheet var targetSheet = ss.getSheetByName("Sheet1"); // Change "Sheet1" to the actual name of your sheet var lastRow = sourceSheet.getLastRow(); var items = sourceSheet.getRange("A2:A" + lastRow).getValues(); var prices = sourceSheet.getRange("C2:C" + lastRow).getValues();

    var columns = ["G","K", "O", "S", "W", "AA"]; // Columns where the IFS function will be written

    for (var i = 0; i < columns.length; i++) { var column = columns[i]; var functionString = "="+"IFNA("+"IFS(";

    for (var j = 0; j < items.length; j++) {
      var item = items[j][0];
      var price = prices[j][0];
      if (column === "G") {
        functionString += 'D2="' + item + '", ' + price;
      }
      else if (column === "K") {
        functionString += 'J2="' + item + '", ' + price;
      }
      else if (column === "O") {
        functionString += 'N2="' + item + '", ' + price;
      }
      else if (column === "S") {
        functionString += 'R2="' + item + '", ' + price;
      }
      else if (column === "W") {
        functionString += 'V2="' + item + '", ' + price;
      }
      else if (column === "AA") {
        functionString += 'Z2="' + item + '", ' + price;
      }
      
      if (j !== items.length - 1) {
        functionString += ', ';
      }
    }
    
    functionString += ")," + "\"" + "\"" + ")";
    targetSheet.getRange(column + "2:" + column).setFormula(functionString);
    

    } }

  5. User

    this code have a problem as it duplicates items in column: function updateDropdown() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet1 = ss.getSheetByName("Sheet1"); var sheet2 = ss.getSheetByName("Sheet2");

    var columns = ["D", "H", "L", "P", "T", "X"]; // Array of columns to update

    for (var i = 0; i < columns.length; i++) { var column = columns[i];

    var existingValues = getDropdownValues(sheet1, column + "2"); // Get existing dropdown values in the current column
    var newValues = sheet2.getRange("A2:A").getValues().filter(String); // Get non-empty values from column A in Sheet2
    
    // Combine existing values with new values
    var combinedValues = existingValues.concat(newValues);
    
    var range = sheet1.getRange(column + "2:" + column); // Adjust the range as needed
    
    // Clear existing validation before setting the updated list
    range.clearDataValidations();
    
    var rule = SpreadsheetApp.newDataValidation().requireValueInList(combinedValues).build();
    range.setDataValidation(rule);
    

    } }

    function getDropdownValues(sheet, cell) { var range = sheet.getRange(cell); var rule = range.getDataValidation();

    if (rule != null && rule.getCriteriaType() === SpreadsheetApp.DataValidationCriteria.VALUE_IN_LIST) { return rule.getCriteriaValues()[0]; // Get the list of values } else { return []; } }

  6. Assistant

    function updateDropdown() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet1 = ss.getSheetByName("Sheet1"); var sheet2 = ss.getSheetByName("Sheet2");

    var columns = ["D", "H", "L", "P", "T", "X"]; // Array of columns to update

    for (var i = 0; i < columns.length; i++) { var column = columns[i];

    var existingValues = getDropdownValues(sheet1, column + "2"); // Get existing dropdown values in the current column
    var newValues = sheet2.getRange("A2:A").getValues().filter(String); // Get non-empty values from column A in Sheet2
    
    // Combine existing values with new values and remove duplicates
    var combinedValues = Array.from(new Set(existingValues.concat(newValues)));
    
    var range = sheet1.getRange(column + "2:" + column); // Adjust the range as needed
    
    // Clear existing validation before setting the updated list
    range.clearDataValidations();
    
    var rule = SpreadsheetApp.newDataValidation().requireValueInList(combinedValues).build();
    range.setDataValidation(rule);
    

    } }

    function getDropdownValues(sheet, cell) { var range = sheet.getRange(cell); var rule = range.getDataValidation();

    if (rule != null && rule.getCriteriaType() === SpreadsheetApp.DataValidationCriteria.VALUE_IN_LIST) { return rule.getCriteriaValues()[0]; // Get the list of values } else { return []; } }

  7. User

    I want to write a formula to sum income in column B if the delivery date in column D is between 9/19/2023 and 10/19/2023 and repeat it for each month and put the results starting from I8 column

  8. Assistant

    function calculateMonthlyIncome() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName("Sheet1"); // Change "Sheet1" to the actual name of your sheet var startDate = new Date("9/19/2023 00:00:00"); // Start date for the range var endDate = new Date("10/19/2023 23:59:59"); // End date for the range

    var months = { "January": { start: new Date("1/1/2023 00:00:00"), end: new Date("1/31/2023 23:59:59") }, "February": { start: new Date("2/1/2023 00:00:00"), end: new Date("2/28/2023 23:59:59") }, "March": { start: new Date("3/1/2023 00:00:00"), end: new Date("3/31/2023 23:59:59") }, // Add more months as needed };

    var outputRow = 8; // Row to start writing the results

    for (var month in months) { var monthStart = months[month].start; var monthEnd = months[month].end; var filteredValues = sheet.getRange("B:B").getValues().filter(function(value, index) { var deliveryDate = sheet.getRange(index + 1, 4).getValue(); return deliveryDate >= monthStart && deliveryDate <= monthEnd; });

    var sum = filteredValues.reduce(function(acc, val) {
      return acc + val[0];
    }, 0);
    
    sheet.getRange(outputRow, 9).setValue(sum); // Write the sum to column I starting from row 8
    outputRow++;
    

    } }