getLastRow() stops at row 68

I have this function that is supposed to pull form data from one sheet then add it to another but it stops at row 68.

function fData(){
  const os = SpreadsheetApp.openById("ID of Sheet with data to copy");
  const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Data");
  const dataToCopy = os.getSheetByName("Form Data").getRange(1,1,os.getLastRow(),7).getValues();
  const dataDst = ss.getRange(1,1,os.getLastRow(),7).clearContent().setValues(dataToCopy);
//  Logger.log(dataToCopy);

}

When I change os.getLastRow() to 192 in both locations where it appears in the function, all the information is copied over correctly. I would like to not have to specify the number of rows every time I run the function.

I think there is something wrong with my implementation of getLastRow() or the fact that both the copy and paste sheets share the same name but I am unsure if that is what causes the issue.

Here is the solution that got the desired result. There might be a better way but this worked.

function fData(){
  const os = SpreadsheetApp.openById("ID of Sheet with data to copy");
  const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Data");
  const dataToCopy = os.getSheetByName("Form Data").getDataRange().getValues();
  const lr = os.getSheetByName("Form Data").getDataRange().getLastRow();
  const dataDst = ss.getRange(1,1,lr,7).clearContent().setValues(dataToCopy);
//  Logger.log(dataToCopy);

}

I used getDataRange to pull the data, then defined lr as the last row of that data set. If you just replace the first instance of getLastRow with getDataRange you run into an error in which the target range and data set do not match in rows and columns and the function with push an error.

Answer

getLastRow(): this will get the last row in a Google Sheet that has data in it. It determines the last row based on the last available row value in all columns in the spreadsheet.
getDataRange(): this will get the range up to the last row and column with values in it.

Use getDataRange() instead. One of your columns must not have data in row 68.
https://developers.google.com/apps-script/reference/spreadsheet/sheet#getdatarange

Advertisement

Answer