[Google script]How to assign generated edit response URL from a form submission and then assign it to a button in confirmation email?

Question

[Edited to avoid misunderstanding and baseless accusation] I am developing a booking system for a short-term housing by using Google script. So when someone submit a form, they will get modified confirmation email along with a button with edit response URL. Process flow:

Someone, lets call him A submitted a form.
edit response URL is generated on the sheet
URL generated is sent to A's email containing edit response URL

How to assign generated edit response URL from a form submission and then assign it to a button in confirmation email? Thank you in advance!

//Email main template
    function draftEmail(request){
    
    //generate edit URL into spreadsheet
    var formRes = FormApp.openById('form id'); //
    var sheetRes = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet name');
    var data = sheetRes.getDataRange().getValues();
    var urlCol = 11;
    var responses = formRes.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))]:'']);
    }
    sheetRes.getRange(2, urlCol, resultUrls.length).setValues(resultUrls);
    
      //button link --> I want to assign the link generated from function above, and then assign it to buttonlink below
      request.buttonLink = 'LINK TO EDIT RESPONSE URL GENERATED FROM ABOVE FUNCTION';
      request.buttonText = "Reschedule";
      switch (request.status) {
        case "Approve":
          request.subject = "Confirmation: " + request.room + " Reservation for " + request.dateString;
          request.header = "Confirmation";
          request.message = "Your room reservation has been scheduled. Total payment: " + request.total + " JPY";
          break;
    
        case "Conflict":
          request.subject = "Conflict with " + request.room + "Reservation for " + request.dateString;
          request.header = "Conflict";
          request.message = "There is a scheduling conflict. Please pick another room or time."
          request.buttonText = "Reschedule";
          break;
      }
    }

This is the email.gs part

function makeEmail(request) {
  return (
    '<!DOCTYPE html><html><head><base target="_top"></head><body><div style="text-align: center;' +
    'font-family: Arial;"><div id="center" style="width:300px;border: 2px dotted grey;background:' +
    '#ececec; margin:25px;margin-left:auto; margin-right:auto;padding:15px;"><img src="https://upload.' +
    "" +
    '.svg.png"width="180" style="margin:10px 0px"><br /><div style=" border: 2px dotted grey;' +
    'background:white;margin-right:auto; margin-left:auto; padding:10px;"><h2>' +
    request.header +
    "</h2><h3>" +
    request.message +
    "<br /><br/>" +
    request.name +
    "<br />" +
    "AMS Room " + request.room +
    "<br />" +
    "Check-in: " + request.dateString +
    "<br />" +
     "Check-out: " + request.dateOutString+
    "<br /></h3><br />" +
    '<a href="' +
    request.buttonLink +
    '" class="btn" style="-webkit-border-radius: 28;' +
    "-moz-border-radius: 5;border-radius: 5px;font-family: Arial; color: #ffffff;font-size: 15px;" +
    'background: #ff7878;padding:8px 20px 8px 20px;text-decoration: none;">' +
    request.buttonText +
    '' +
  );
}

I don't see where you're even trying to utilize the email at all. What is your specific question regarding putting a url into an email? — Cooper
Hi Cooper, thank you for your comment. I want the system to send a modified confirmation email containing logo (I have attached the image sample in the main post. So the function goes something like this: 1) someone, lets call him A submitted a form 2) edit response URL is generated on the sheet 3) URL generated is sent to A's email containing edit response URL regards, — Sarimu
Specific question: how are you getting the generated edit response URL from a form submission? // there's a lot of code here to go through, could you please reduce it to the response URL generation and email code only? — I hope this is helpful to you
Hi Rafa, thank you for the comment, I included all other code because someone accused me of just wanting to get code written for free...so I included most part of code here. The response URL generation is marked with //generate edit URL into spreadsheet (on the beginning part of this post). — Sarimu

Micah Micah · Accepted Answer · 2021-04-12T08:57:00

Would this be close to what you are trying to do? This shows putting a link in a confirmation email to the editable submitted form. How to send email with edit URL from a google form?

This one also goes into sending an edit URL by email to a submitter: https://webapps.stackexchange.com/questions/50009/is-there-a-way-to-keep-track-of-the-url-used-to-edit-responses-in-google-form

There's also a google support thread here about users editing responses. Fifth comment also has a link to some script: https://support.google.com/docs/thread/9968940?hl=en

EDIT: This post here popped up in related links on the right of the page, in case you have any issues with making sure the URL generates before the email is sent: Google Form. Confirmation Email Script. With edit url

[Google script]How to assign generated edit response URL from a form submission and then assign it to a button in confirmation email?

2 Answers