Skip to content

Instantly share code, notes, and snippets.

@atalv
Last active April 12, 2020 07:59
Show Gist options
  • Save atalv/b6c74ed00f68f38b5856a4aa3baa6275 to your computer and use it in GitHub Desktop.
Save atalv/b6c74ed00f68f38b5856a4aa3baa6275 to your computer and use it in GitHub Desktop.
Google Apps Script code to add Google Form edit response links in the linked Google Sheet. It will also append the form edit links for newly added responses - instead of generating links for all the responses each time.

Assuming first row of the sheet where form responses are recorded has column headers

Assuming first column of the sheet where form responses are recorded has timestamp values of Google Form response submit time

  1. Copy the code in assignEditUrls.gs.
  2. Create new script from the Google Sheet which is linked with the Google Form you created.
    2.a) Click on Tools -> Script editor
  3. Remove everything and paste the above code
  4. Update the sheet name in line 6 of the code (if needed).
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses 1');
    4.a) It should match with the sheet name of the Google Sheet where Google Form responses are being recorded automatically. By default the sheet name is registered as what is mentioned in this code.
  5. Save the script and run the function assignEditUrls
    5.a) Approve the required authentications (if asked) - the whole script is written here, no malicious codes!
  6. Check the column named editFormURL in the destination sheet!
You can also setup triggers to automatically append editFormURL for new responses whenever they are submitted
  1. Click on Edit -> Current project's triggers in the Google Script page where you pasted the above code
  2. Click on Add Trigger
  3. Select assignEditUrls under Choose which function to run
  4. Select On form submit under Select event type
  5. Click Save. Done!
function assignEditUrls() {
// Function to add edit form link for each response recorded in sheets. Will append for new responses.
var formUrl = SpreadsheetApp.getActiveSpreadsheet().getFormUrl();
var form = FormApp.openByUrl(formUrl);
// Provide the sheet name where Google Form responses are being recorded
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses 1');
var data = sheet.getDataRange().getValues();
// Identifying existing edit form url column - 'editFormURL' - if exists then add edit links this column, else add a new column
// Assuming first row contains headers
var findCol = sheet.getRange(1, 1, 1, sheet.getLastColumn()).createTextFinder('editFormURL').matchEntireCell(true).findNext();
if (findCol == null){
urlCol = sheet.getLastColumn()+1;
sheet.getRange(1, urlCol).setValue("editFormURL");
}
else {
urlCol = findCol.getColumn();
}
;
// Updating only those rows which are not already there in the edit form link column, but in form responses
// Custom functions are being used as defined next in this script - getLastRowSpecial, getLastColSpecial
var urlColRange = sheet.getRange(1, urlCol, sheet.getLastRow()).getValues();
var urlColLastRow = getLastRowSpecial(urlColRange);
// Assuming first column contains recorded timestamps of Form response submit time
var TimestampLastRow = getLastRowSpecial(sheet.getRange(1, 1, sheet.getLastRow()).getValues());
// Collect edit link information only if urlColLastRow < TimestampLastRow
if (urlColLastRow < TimestampLastRow){
var responses = form.getResponses();
// Slicing for last responses without edit url link may not work - if any edit is made in previous record then timestamp for that gets updated!
// var responses = responses.slice(urlColLastRow - TimestampLastRow);
// Alternate way to filter responses accordingly as below
var TimestampsWoUrl = sheet.getRange(urlColLastRow + 1, 1, TimestampLastRow - urlColLastRow).getValues()
var minTimestampWoUrl = new Date(Math.min.apply(null,...TimestampsWoUrl))
// Logger.log(minTimestampWoUrl);
var filterResponses = responses.filter(resp => resp.getTimestamp() >= minTimestampWoUrl);
var timestamps = [], urls = [], resultUrls = [];
var timestamps = filterResponses.map(ele => ele.getTimestamp().setMilliseconds(0));
var urls = filterResponses.map(ele => ele.getEditResponseUrl());
for (var j = urlColLastRow ; j < data.length; j++) {
resultUrls.push(['=HYPERLINK("' + [data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:''] + '","Edit")']);
}
sheet.getRange(urlColLastRow + 1, urlCol, resultUrls.length).setValues(resultUrls);
}
};
function getLastRowSpecial(range){
// Extract last row number of a given range having non-missing value, 0 if all rows are missing
var rowNum = 0;
for(var col=0; col < range[0].length; col++){
var blank = false;
for(var row = rowNum; row < range.length; row++){
if(range[row][col] === "" && !blank){
rowNum = row;
blank = true;
}else if(range[row][col] !== ""){
blank = false;
};
};
};
// if no blank cases are found then extracting the number of rows -- as all rows are filled with some value
if (!blank)
rowNum = range.length
return rowNum;
};
function getLastColSpecial(range){
// Extract last column number of a given range having non-missing value, 0 if all columns are missing
var colNum = 0;
for(var row = 0; row < range.length; row++){
var blank = false;
for(var col = colNum; col < range[0].length; col++){
if(range[row][col] === "" && !blank){
colNum = col;
blank = true;
}else if(range[row][col] !== ""){
blank = false;
};
};
};
// if no blank cases are found then extracting the number of columns -- as all columns are filled with some value
if (!blank)
colNum = range[0].length
return colNum;
};
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment