Timestamp from Google Forms returns NaN with getTime()

Question

I have a google form which returns responses to a google spreadsheet named 'Form responses 1' for which column A is the timestamp (this shows up as '25/06/2015 21:36:00' in the google spreadsheet cell) and column G and I contains numerical values of which I am interested in the last row with not "" values. The last row with not "" values in both columns G and I may not be the exact same row.

1) Is there a way not to redefine "var lastRow = spreadsheet.getSheetByName("Form responses 1").getLastRow();" in line 11? I found that this line is necessary to reset the definition for var lastRow.

Without it, the last row with not "" values in column I would be wrong (it gets the next non "" row after moving on from column G's last row with non "" values instead of counting upwards from the last row of column I).

2) I am getting a NaN for the LastNonZeroDate for both columns G and I for which I am trying to retrieve in milliseconds with the the getTime() function. How can I remedy this?

If I retrieve the value of "new Date('responsesvalues[lastRow - 1][0]')getTime()" in a msg box instead, it gives me "Thu Jun 25 2015 21:35:59 GMT+0800 (HKT)".

Thanks in advance for any help.

// get the data range
var lastRow = spreadsheet.getSheetByName("Form responses 1").getLastRow();
var responsesvalues = spreadsheet.getSheetByName("Form responses 1").getRange("A1:Q" + lastRow).getValues();

// get the last row in column G with non-zero value and corresponding date
for (; responsesvalues[lastRow - 1][6] == "" && lastRow > 0; lastRow--) {}
var GLastNonZero = responsesvalues[lastRow - 1][6];
var GLastNonZeroDate = new Date('responsesvalues[lastRow - 1][0]').getTime();

// get the last row in column I with non-zero value and corresponding date
var lastRow = spreadsheet.getSheetByName("Form responses 1").getLastRow();
for (; responsesvalues[lastRow - 1][8] == "" && lastRow > 0; lastRow--) {}
var ILastNonZero = responsesvalues[lastRow - 1][8];
var ILastNonZeroDate = new Date('responsesvalues[lastRow - 1][0]').getTime();

new Date('responsesvalues[lastRow - 1][0]') Why the quotes? — Andreas
Thank you. That solves the date problem. Are you able to resolve the other question in 1)? — Mike
Don't change the value of lastRow in the loop ...; lastRow--). Use an extra variable instead. — Andreas
Thanks. Is the below code what you had in mind or is there something better? for (var i = lastRow; responsesValues[i - 1][6] == "" && i > 1; i--) {} var GLastNonZero = responsesValues[i - 1][6]; var GLastNonZeroDate = new Date(responsesValues[i - 1][0]).getTime(); for (var j = lastRow; responsesValues[j - 1][8] == "" && j > 1; j--) {} var ILastNonZero = responsesValues[j - 1][8]; var ILastNonZeroDate = new Date(responsesValues[j - 1][0]).getTime(); — Mike
Is there any way to format the above code so it's more readable? Also, in the code above, I realized that I get an error saying "6 is undefined" if [i - 1] or [j - 1] is changed to [i] and [j]. Why is that so? — Mike

Andreas Andreas · Accepted Answer · 2015-06-29T09:57:05

Without a spreadsheet to test this is just a "should probably work"-attempt

1) Use an extra variable to calculate the last row for each of the columns.
I'm using i to calculate the difference between the last filled row of the spreadsheet and the last filled row of the columns. The documentation of .getLastRow() doesn't say if the value is zero based or not. Hence the comments in the script

2) Remove the quotes in the new Date() calls

// get the data range
var lastRow = spreadsheet.getSheetByName("Form responses 1").getLastRow();
var responsesvalues = spreadsheet.getSheetByName("Form responses 1").getRange("A1:Q" + lastRow).getValues();
var i;

// get the last row in column G with non-zero value and corresponding date
for (i = 0; i <= lastRow; i++) {
    if (responsesvalues[lastRow - 1][6] !== "") {
        break;
    }
}
var GLastNonZero = responsesvalues[lastRow - i - 1][6];     // not sure about the -1
var GLastNonZeroDate = new Date(responsesvalues[lastRow - i - 1][0]).getTime();         // not sure about the -1

// get the last row in column I with non-zero value and corresponding date
for (i = 0; i <= lastRow; i++) {
    if (responsesvalues[lastRow - 1][8] !== "") {
        break;
    }
}
var ILastNonZero = responsesvalues[lastRow - i- 1][8];      // not sure about the -1
var ILastNonZeroDate = new Date(responsesvalues[lastRow - i - 1][0]).getTime();     // not sure about the -1

Timestamp from Google Forms returns NaN with getTime()

1 Answers