rubenrivera · September 2, 2024 23:26 · Feb 18, 2017 · Feb 18, 2017 · Feb 18, 2017 · Feb 18, 2017
diff --git a/question.md b/question.md
@@ -1,4 +1,5 @@
 > Revision 3 http://webapps.stackexchange.com/revisions/89551/3
+
 # Show URL used to edit responses from a Google Form in a Google Spreadsheet by using a script
 I'm trying to make the edit URL for a Google Form automatically populate in the response Google Sheet that it's attached to. I've seen [this](http://webapps.stackexchange.com/questions/50009/is-there-a-way-to-keep-track-of-the-url-used-to-edit-responses-in-google-form) already and want to use it, but I'm having issues trying to figure out where exactly to put the script.
 

diff --git a/response.md b/response.md
@@ -1,4 +1,5 @@
 > Revision 5 http://webapps.stackexchange.com/revisions/89566/5
+
 #Short answer
 The [link](http://webapps.stackexchange.com/questions/50009/is-there-a-way-to-keep-track-of-the-url-used-to-edit-responses-in-google-form) points to a Q&A that has two answers but non-of them looks to me to be appropiate. I.E. the code of one of answers is incomplete and both of them send emails, so the adoption is too complex to be used as a starting point. 
 

diff --git a/question.md b/question.md
@@ -0,0 +1,7 @@
+> Revision 3 http://webapps.stackexchange.com/revisions/89551/3
+# Show URL used to edit responses from a Google Form in a Google Spreadsheet by using a script
+I'm trying to make the edit URL for a Google Form automatically populate in the response Google Sheet that it's attached to. I've seen [this](http://webapps.stackexchange.com/questions/50009/is-there-a-way-to-keep-track-of-the-url-used-to-edit-responses-in-google-form) already and want to use it, but I'm having issues trying to figure out where exactly to put the script.
+
+I've tried putting it in the script editor in the Google Spreadsheet that I would like the URL to appear in, but I'm not sure where to go from there. In the script editor I've tried to test it as an add-on but that didn't end up working.
+
+I have little experience with scripts and the script editor in Google Sheets. As a final note I am using one other add-on called AutoCrat in the same Google Spreadsheet.  
diff --git a/response.md b/response.md
@@ -0,0 +1,72 @@
+> Revision 5 http://webapps.stackexchange.com/revisions/89566/5
+#Short answer
+The [link](http://webapps.stackexchange.com/questions/50009/is-there-a-way-to-keep-track-of-the-url-used-to-edit-responses-in-google-form) points to a Q&A that has two answers but non-of them looks to me to be appropiate. I.E. the code of one of answers is incomplete and both of them send emails, so the adoption is too complex to be used as a starting point. 
+
+Below is a script that is easier to adopt.
+
+#Instructions
+1. Create a test form and note the **form ID** (between `../d/` and `/edit` in the url).
+[![New form][1]][1]
+
+2. Set the form to send responses to a new spreadsheet.
+[![Connect a spreadsheet][2]][2]
+
+3. You'll be redirected to the new spreadsheet. Note the active **sheet name** (rename it if you want) and add a **header** to the column to be used to hold the response edit URLs, i.e. `Edit Url`. NOTE: Capitalization is very important, so be very careful on how do you write it.
+[![Responses spreadsheet][3]][3]
+
+4. Go to `Tools > Script editor` to add a Google Apps Script project bounded to the spreadsheet.
+[![Spreadsheet scripts][4]][4]
+
+5. Replace the default code with the code below (remember to edit the global variables according to your case).
+[![Script code][5]][5]
+
+6. Save the project, and then add an installable form summit trigger (if you get an "Authorization required" dialog, click "Review Permissions" and then "Allow").
+[![Form submit trigger][6]][6]
+
+7. Submit some sample responses to test the solution.
+
+#Code
+
+<!-- language: lang-js -->
+
+    /*
+     * Global Variables
+     */
+
+    // Form URL
+    var formURL = 'https://docs.google.com/forms/d/form-id/viewform';
+    // Sheet name used as destination of the form responses
+    var sheetName = 'Form Responses 1';
+    /*
+     * Name of the column to be used to hold the response edit URLs 
+     * It should match exactly the header of the related column, 
+     * otherwise it will do nothing.
+     */
+    var columnName = 'Edit Url' ;
+    // Responses starting row
+    var startRow = 2;
+
+    function getEditResponseUrls(){
+      var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
+      var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues(); 
+      var columnIndex = headers[0].indexOf(columnName);
+      var data = sheet.getDataRange().getValues();
+      var form = FormApp.openByUrl(formURL);
+      for(var i = startRow-1; i < data.length; i++) {
+        if(data[i][0] != '' && data[i][columnIndex] == '') {
+          var timestamp = data[i][0];
+          var formSubmitted = form.getResponses(timestamp);
+          if(formSubmitted.length < 1) continue;
+          var editResponseUrl = formSubmitted[0].getEditResponseUrl();
+          sheet.getRange(i+1, columnIndex+1).setValue(editResponseUrl);
+        }
+      }
+    }
+
+
+  [1]: https://i.stack.imgur.com/ThDwf.png
+  [2]: https://i.stack.imgur.com/w72gk.png
+  [3]: https://i.stack.imgur.com/sZMns.png
+  [4]: https://i.stack.imgur.com/nlfsE.png
+  [5]: https://i.stack.imgur.com/6IL3A.png
+  [6]: https://i.stack.imgur.com/YL9Zf.png