|
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; |
|
}; |