Could someone take a look at this section of my script and provide some feedback? The script is triggered whenever a response is submitted, and its purpose is to turn a column of data containing text strings into clickable hyperlinks to edit each response. However, the issue is that the hyperlinks are directing to the wrong responses, and with each new submission, the offset increases by 1. Here's an illustration:
ORIGINAL
- Entry 1 - Hyperlink Leads to Entry 1
- Entry 2 - Hyperlink Leads to Entry 2
- Entry 3 - Hyperlink Leads to Entry 3
- Entry 4 - Hyperlink Leads to Entry 4
AFTER 1 SUBMIT
- Entry 1 - Hyperlink Leads to Entry 2
- Entry 2 - Hyperlink Leads to Entry 3
- Entry 3 - Hyperlink Leads to Entry 4
- Entry 4 - Hyperlink Leads to Entry 1
AFTER 2 SUBMITS
- Entry 1 - Hyperlink Leads to Entry 3
- Entry 2 - Hyperlink Leads to Entry 4
- Entry 3 - Hyperlink Leads to Entry 1
- Entry 4 - Hyperlink Leads to Entry 2
Below is the code snippet used to create these hyperlinks:
var _sht = SpreadsheetApp.openById("XXX").getSheetByName("Sheet");
var formID = "YYY";
var form = FormApp.openById(formID);
var formResponses = form.getResponses();
for (var ii=0; ii<formResponses.length; ii++) {
var tradeName = _sht.getRange(ii+2, 2).getValue();
_sht.getRange(ii+2, 2).setFormula('=HYPERLINK("' + formResponses[ii].getEditResponseUrl() + '","' + tradeName + '")');
}