Type error in Google Sheet script

0
votes

In the Google sheet script editor, I'm using the code below to insert the Edit response link in the spreadsheet along with the form data.

function assignEditUrls() {
  var form = FormApp.openById('xxxxxxxxxxxxxxxxxxxxxxxx');
    //enter form ID here

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses');

    //Change the sheet name as appropriate
  var data = sheet.getDataRange().getValues();
  var urlCol = 3; // column number where URL's should be populated; A = 1, B = 2 etc
  var responses = form.getResponses();
  var timestamps = [], urls = [], resultUrls = [];

  for (var i = 0; i < responses.length; i++) {
    timestamps.push(responses[i].getTimestamp().setMilliseconds(0));
    urls.push(responses[i].getEditResponseUrl());
  }
  for (var j = 1; j < data.length; j++) {
    resultUrls.push([data[j][1]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:'']);
  }
  sheet.getRange(2, urlCol, resultUrls.length).setValues(resultUrls);  
}

Whilst this was working as it should up till last week, when the script is now run I get the following error:

TypeError: Cannot find function setMilliseconds in object . (line 18, file "Code")

Any thoughts or alternative solutions would be gratefully received.

3
google-apps-scriptgoogle-sheetsgoogle-forms
Double of stackoverflow.com/questions/26521772/… - Riël
@Riel I don't believe this is a double, as this question is about why 'TypeError: Cannot find function setMilliseconds in object . (line 18, file "Code")', whereas the previous was about why 'TypeError: Cannot call method "getSheetByName" of null. (line 5, file "Code")Dismiss' was occurring. Similar, yes, but not quite a dupe I think. - HDCerberus
Yes, thanks it is a slightly different error I am getting which is why I posted a separate question. It is an old version of Google Sheets that i'm using for it if that makes a difference? - Tariq Ali

3 Answers

0
votes

This code runs for me with no issues and no modification, so whatever your error is, it's either temporary, or not with this portion of the script.

Edit: Just tried it again to be doubly sure and the execution transcript is confirming for me: '[14-11-18 23:17:52:820 GMT] Execution succeeded [0.581 seconds total runtime]' and I'm seeing the links appear in the spreadsheet no issue.

0
votes

I have had this problem as well. I don't know why this works but deleting all of the extra rows below the responses and it corrects the issue.

0
votes

You should change 

for (var j = 1; j < data.length; j++) {
    resultUrls.push(['=HYPERLINK("' + [data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:''] + '","Edit")']);
  }
  sheet.getRange(2, urlCol, resultUrls.length).setValues(resultUrls);

j = 1 -> you have to change the number 1 to the number of the first row that you have a value from the form -z-1. For example, if you have added some more rows on top of the ones used filled by the form, let's say you added one row, then 1 should become 2 (1+1). Also, the number 2 in the last line should get also +1 and thus should become 3.

I have changed my code to this:

var pos1st = 3; // row number where the first response is located; 1 = 1, 2 = 2, etc
for (var j = pos1st-1; j < data.length; j++) {
    resultUrls.push(['=HYPERLINK("' + [data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:''] + '","Edit")']);
  }
  sheet.getRange(pos1st, urlCol, resultUrls.length).setValues(resultUrls);