Range Length in Google Apps Script

Question

I want to run a script that copies a sheet data to a master sheet (append all my sheets). The first part of copying and pasting is working but I want to add a column which tells me the name of the origin sheet. I wrote a loop for it but nothing is happening when I executing the script (only the copy and paste). This is my whole code:

function appendSheet() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sh = ss.getActiveSheet();
   var reportLastRow = sh.getLastRow()
   var reportLastColumn = sh.getLastColumn()
   var reportData = sh.getSheetValues(3,1,reportLastRow,reportLastColumn);
   var recordsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("2020 Data");
   var lastRow = recordsSheet.getLastRow();
   //var recordLastRow = sh.getLastRow();
   var recordLastColumn = recordsSheet.getLastColumn();
   var reportSheetName = sh.getSheetName();
   recordsSheet.getRange(lastRow + 1,1,reportLastRow,reportLastColumn).setValues(reportData);
   var arrayLength = (lastRow - reportData.length);
   for (var i =  0 ; i <= arrayLength ; i ++) {
      var taskDateCell = recordsSheet.getRange(arrayLength - i, recordLastColumn);
      taskDateCell.setValues(reportSheetName);
   }
}

Answer 1

Your code has several problems you should fix

1. Be aware of the fact that the method getRange() expects the syntax firstRow, firstColumn, numRows, numColumns - not firstRow, firstColumn, lastRow, lastColumn. You need to adjust your range and your function getSheetValues() (I assume it is you custom funciton based on the method getRange() accordingly.
2. If you assign a value to one cell at a time taskDateCell, you should use setValue() instead of setValues()
3. It seems like your definition of arrayLength might not be right. Test it by logging it.
4. Your main problem:

You define:

for (var i = arrayLength ; i < arrayLength ; i ++)

In other words:

Set i to arrayLength and iterate while i is smaller than arrayLength.

This condition is never fullfilled, and thus the number of iterations will be zero.

As a genral advice: Implement in your code many logs - to visualize important values, such a range notations and length of arrays, or counter variables in a loop - this will help you to find bugs faster.

Answer 2

Explanation:

• First of all I optimized your code. You have unnecessary lines of code which make your code difficult to be understood but also slow. The optimizations involve reducing the number of lines to the minimum, defining constant variables, making the variable names more descriptive and finally getting rid of the for loop by replacing it with a more efficient approach.

• Another correction would be at this line: sh.getSheetValues(3,1,reportLastRow,reportLastColumn); Here you are starting from the third row but you are getting two rows extra; reportLastRow is the number of rows you want to get but since you are starting from the third row you need to deduct 2.

• To answer your question, one way to solve your problem is to set the name of the report to the last column of the records sheet where you entered the data from the report sheet. Since you want to add the same value (sheet name) to every row, you can select a 2D range but use setValue.

• I am not a big fan of getActiveSheet in const report_sh = ss.getActiveSheet(); since this line is assuming that you have selected the desired sheet (report sheet) in the UI. Please be careful with that, otherwise change that line to something like that:

  const report_sh = ss.getSheetByName('report sheet');

  and of course adjust 'report sheet' to the name of the sheet you want to append.

Solution:

function appendSheet() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const records_sh = ss.getSheetByName("2020 Data");
const report_sh = ss.getActiveSheet();
const reportData = report_sh.getSheetValues(3,1,report_sh.getLastRow()-2,report_sh.getLastColumn());
const records_lr = records_sh.getLastRow();
records_sh.getRange(records_lr+1,1,reportData.length,reportData[0].length).setValues(reportData);
SpreadsheetApp.flush();
records_sh.getRange(records_lr+1,records_sh.getLastColumn()+1,reportData.length,1).setValue(report_sh.getSheetName());
}