I need to return quarterly sums and year to date sums for each line item from Sheet1 to Sheet2

Question

I would like to use Google appscript coding to:

1. On Sheet1 -indentify the last column that has a value
2. Sum that column for each row with the previous 2 columns (start w/ row 2. Row1 is header)
3. Paste these summed values on Sheet2 Column D
4. After Finding the last column that has a value sum all the previous columns
5. Paste these summed values on Sheet2 Column E

Sheet1

• Col A is Street
• Col B is the first entry for all the streets
• Col C will be the next weeks entry. The chart grows by columns each week.

Sheet2

• Col A is zipcode
• Col B is Street
• Col C is Last Month
• Col D is Quarterly
• Col E is year To Date

https://docs.google.com/spreadsheets/d/1qA6L10kdhReW9JNksSQUPn2e24ZOdTOAnO25d-ksNos/edit?usp=sharing failed attemps

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName("Sheet1");
  var sheet2 = ss.getSheetByName("Sheet2");
  
  var lastColumn = sheet1.getLastColumn();
    if (lastColumn < 3) {
    SpreadsheetApp.getUi().alert("Sheet1 does not have at least 3 columns.");
   return;
  }
  var lastRow = sheet1.getLastRow();
  var rangeToSum = sheet1.getRange(2, lastColumn - 2, lastRow - 1, 3); 
  var valuesToSum = rangeToSum.getValues();
  var summedValues = [];
  
   for (var i = 0; i < valuesToSum.length; i++) {
    var sum = valuesToSum[i][0] + valuesToSum[i][1] + valuesToSum[i][2];
    summedValues.push([sum]);
  }
    var destinationRange = sheet2.getRange(2, 4, summedValues.length, 1);
  destinationRange.setValues(summedValues);
  }

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName("Sheet1");
  var sheet2 = ss.getSheetByName("Sheet2");
  
  // Get the last column with a value in Sheet1
  var lastColumn = sheet1.getLastColumn();
  
  if (lastColumn < 2) {
    // If there are less than 2 columns, we cannot sum the columns starting from B
    SpreadsheetApp.getUi().alert("Sheet1 does not have at least 2 columns.");
    return;
  }
  
  // Get the last row with data in Sheet1
  var lastRow = sheet1.getLastRow();
  
  // Get the range of columns from B to the last column starting from row 2 to the last row
  var rangeToSum = sheet1.getRange(2, 2, lastRow - 1, lastColumn - 1);
  var valuesToSum = rangeToSum.getValues();
  
  // Create an array to store the summed values
  var summedValues = [];
  
  // Sum each row's values from columns B to the last column
  for (var i = 0; i < valuesToSum.length; i++) {
    var sum = 0;
    for (var j = 0; j < valuesToSum[i].length; j++) {
      sum += valuesToSum[i][j];
    }
    summedValues.push([sum]);
  }
  
  // Set the summed values in Sheet2 column E starting from row 2
  var destinationRange = sheet2.getRange(2, 5, summedValues.length, 1); // Column E is the 5th column
  destinationRange.setValues(summedValues);
}

Answer 1

In your situation, how about the following sample script?

Sample script:

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [sheet1, sheet2] = ["Sheet1", "Sheet2"].map(s => ss.getSheetByName(s));
  const [, ...values1] = sheet1.getDataRange().getDisplayValues();
  const obj = new Map(values1.map(([h, b, ...c]) => {
    const temp = c.reverse().findIndex(String);
    return [h, [c.slice(temp, temp + 2).reduce((t, e) => t += Number(e), 0), [b, ...c].reduce((t, e) => t += Number(e), 0)]];
  }));
  const range2 = sheet2.getRange("A2:E" + sheet2.getLastRow());
  const values2 = range2.getValues().map(r => obj.has(r[1]) ? [...r.slice(0, 3), ...obj.get(r[1])] : r);
  range2.setValues(values2);
}

Testing:

When this script is run, the following result is obtained.

Note:

• I confirmed that this sample script can be used in your provided spreadsheet. If you change the Spreadsheet, this script might not be able to be used. Please be careful about this.

References:

• map()
• Map