Google Sheets/Forms Script Due Date Reminder email Sends Wrong Response Edit Link

Question

The system I set up does the following:

• a user submits a form
• a confirmation email is sent with a link to edit the response (so far so good)
• in the form, there is a 'due date' question
• a trigger script scans these dates each day
• when the due date has come, it sends a reminder email (this also happens properly, but)
• in this reminder email, the edit link is repeated, so that the user doesn't have to search for the previous mail

Sadly this link is sent out wrong. Instead of linking to the proper response, it gives the link to the response that has been edited (submitted) last.

Here is the script:

function sendReminderEmail() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2;  // First row of data to process
var numRows = sheet.getLastRow()-1;   // Number of rows to process
// Fetch the range of cells A2:B3
var dataRange = sheet.getRange(startRow, 1, numRows, sheet.getLastColumn());
// Fetch values for each row in the Range.
var data = dataRange.getValues();
//Logger.log(data)

var form = FormApp.openById("IDremovedByMe");
var formResponses = form.getResponses();
var r = formResponses.length-1;
var editURL = formResponses[r].getEditResponseUrl();
//Get the Edit URL

for (i in data) {
  var row = data[i];
  var date = new Date();
  date.setHours(0);
  date.setMinutes(0);
  date.setSeconds(0);
  //Logger.log(date);
  var sheetDate = new Date(row[13]);
 //Logger.log(sheetDate);
 var Sdate = Utilities.formatDate(date,'GMT+0100','yyyy:MM:dd')
 var SsheetDate = Utilities.formatDate(sheetDate,'GMT+0100', 'yyyy:MM:dd')
     Logger.log(Sdate+' =? '+SsheetDate)
        if (Sdate == SsheetDate){
          var sendTo = row[4];  // Collect email address from the fourth column (number value is always -1).
          var sendMe = "[email protected]";  // Enter the email address where you wish to receive a notification about a reminder sent.
          var sendername = "Auto Formbot"; // Name displayed as the sender.
          var myname = "Formbot"; // Name displayed as the sender to you.
          var messageTo = "Based on the data you entered, the '" +row[6] +"' project with the ID: " +row[1] +" has ended.\n\nPlease mark it as 'Completed' and update the details as soon as it's convenient for you.\n\nYou can edit your data by using the following link:\n" +  editURL + "\n\nThank you for your time.\n\n\nWith kind regards,\nFormbot";
          var messageMe = "The '" +row[6] +"' project with the ID: " +row[1] +" has finished today.\n\nA reminder email has been sent to " +row[4] +".\n\nYou can edit the data by using the following link:\n" +  editURL + "\n\n\nSincerely,\nFormbot";
          // Above is the column (number value is always -1) selected for activity name display.
          var subjectTo = "Please update the '" +row[6] +"' activity data.";
          var subjectMe = "An activity has finished today [ID: " +row[1] +"].";
          MailApp.sendEmail(sendTo, subjectTo, messageTo, {name: sendername});
          MailApp.sendEmail(sendMe, subjectMe, messageMe, {name: myname});
            }    
   }
  }

The problem is obviously in this part:

var form = FormApp.openById("IDremovedByMe");
var formResponses = form.getResponses();
var r = formResponses.length-1;
var editURL = formResponses[r].getEditResponseUrl();

I'm just not sure how to explain the script how to get the appropriate response.

Perhaps my approach is wrong, maybe I should tell the script to scan the form database instead the linked spreadsheet? Any ideas on how to do that?

Answer 1

I think I understand your question and the problem you're facing. I've been working on auto-generated email notices with form edit URLs as well. You are correct that the script you've written is grabbing the URL for the last form submission (including edits) and not the URL for the response identified by date in your function. I'm not sure how you're capturing the URL for the confirmation emails, but I would recommend capturing the URL in another column of the sheet as the submissions come in. Then, you can just pull the URL value from the data array. I've used the following function to log all form submission URLs to a specific column:

function assignEditUrls() {
  var form = FormApp.openById('insert form ID here');
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('insert sheet name here');
  var data = sheet.getDataRange().getValues();
  var urlCol = 10; // 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][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:'']);
    }

  // populates the assigned column with all form response URLs to date
  sheet.getRange(2, urlCol, resultUrls.length).setValues(resultUrls);  
  // gets the URL for the most recent form submission
  var lastURL = sheet.getRange(resultUrls.length, urlCol).getValue();
  Logger.log("URL Logged: "+lastURL);
  return lastURL;
}

Answer 2

So, I chose to insert the URLs directly into the responses sheet and reference them from there.

I used the script found here.

function injectEditURLs() {
  // Form ID:
  var form = FormApp.openById('IDremovedByMe');

  // Name of the (main) sheet and NOT the Sheet file name where the URLs will appear:
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Responses');

  var data = sheet.getDataRange().getValues();
  var urlCol = 11; // The number of the column in which the URL will be inserted; 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][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:'']);
  }
  sheet.getRange(2, urlCol, resultUrls.length).setValues(resultUrls);  
}

Then I just referenced it (and removed the unnecessary bits) in the reminder email script with:

" + row[n] +"

So it now looks and works something like this:

function sendReminderEmail() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2;  // First row of data to process
var numRows = sheet.getLastRow()-1;   // Number of rows to process
// Fetch the range of cells A2:B3
var dataRange = sheet.getRange(startRow, 1, numRows, sheet.getLastColumn());
// Fetch values for each row in the Range.
var data = dataRange.getValues();
//Logger.log(data)

for (i in data) {
  var row = data[i];
  var date = new Date();
  date.setHours(0);
  date.setMinutes(0);
  date.setSeconds(0);
  //Logger.log(date);
  var sheetDate = new Date(row[13]);
 //Logger.log(sheetDate);
 var Sdate = Utilities.formatDate(date,'GMT+0100','yyyy:MM:dd')
 var SsheetDate = Utilities.formatDate(sheetDate,'GMT+0100', 'yyyy:MM:dd')
     Logger.log(Sdate+' =? '+SsheetDate)
        if (Sdate == SsheetDate){
          var sendTo = row[4];  // Collect email address from the fourth column (number value is always -1).
          var sendMe = "[email protected]";  // Enter the email address where you wish to receive a notification about a reminder sent.
          var sendername = "Auto Formbot"; // Name displayed as the sender.
          var myname = "Formbot"; // Name displayed as the sender to you.
          var messageTo = "Based on the data you entered, the '" +row[6] +"' project with the ID: " +row[1] +" has ended.\n\nPlease mark it as 'Completed' and update the details as soon as it's convenient for you.\n\nYou can edit your data by using the following link:\n" + row[10] + "\n\nThank you for your time.\n\n\nWith kind regards,\nFormbot";
          var messageMe = "The '" +row[6] +"' project with the ID: " +row[1] +" has finished today.\n\nA reminder email has been sent to " +row[4] +".\n\nYou can edit the data by using the following link:\n" +  row[10] + "\n\n\nSincerely,\nFormbot";
          // Above is the column (number value is always -1 because A=0) selected for activity name display.
          var subjectTo = "Please update the '" +row[6] +"' activity data.";
          var subjectMe = "An activity has finished today [ID: " +row[1] +"].";
          MailApp.sendEmail(sendTo, subjectTo, messageTo, {name: sendername});
          MailApp.sendEmail(sendMe, subjectMe, messageMe, {name: myname});
            }    
   }
  }