Check if Spreadsheet.getRange() Returns full cells

Question

I have a app script that deals with returning data from a spread sheet. However I am facing a weird error that at time at the beginning of the day the dataRows return via the code below returns empty, even though the actual spreadsheet had rows full of data, and this only happens the first time I try at the beginning of the day, the second time onwards it works fine.

var dataRows = sheet.getRange(2, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();

So for example, if I try in the morning to submit my Google Form, dataRows gives me empty cells, however second time onwards I tried dataRows give me cells with correct data in it, not empty cells.

Notice the first param of getRange() is 2 because index 1 is the heading row, as seen in below image.

Also it appears that even though dataRows.length would have some valid length that actual data in it appears to be empty, for example dataRows[row][0].valueOf() is just empty.

Is there some way to check if the rows that I am getting from dataRows are valid rows not empty cells and If anyone can give suggestions as to why this would be heppening in the first place as I mentioned above, there is data in the spread sheet at specified row and column but getRange() returns me empty cells.

Please help.

Answer 1

Partial answer

Instead of

var dataRows = sheet.getRange(2, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();

try

var data = sheet.getDataRange().getValues()

Reference

• https://developers.google.com/apps-script/reference/spreadsheet/sheet#getdatarange