Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I would like to use Google Forms to keep track of the progress of an application process (similar to what this websites does for immigration applications). One key functionality that I need is to be able to allow respondent to edit their responses as their application progresses, without being able to edit other responses, as this might be prone to vandalism (which is what have happened several time in our current solution using editgrid).

Today I found out that Google Forms allows to edit responses, but it seems this is only possible if you save the link that is shown (only once) after submitting your response. If the user leave that page without saving, I can't find a way to allow him/her to later modify their responses (as there is no email confirmations or whatsoever the user can later refer to).

I wonder, is there a way for the form owner to find this URL and send it to the users upon request? (Via scripting maybe?)

share|improve this question

2 Answers 2

This will save the edit your response URL to a cell in the spreadsheet. put the script in the spreadsheet, not the form. Can also include the var editURL in an email to the user after they click submit to email them the edit URL.

// edit response
  var form = FormApp.openById('XXXXXXXXXXXXXXX');
    //enter form ID here

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses');
    //Change the sheet name as appropriate

  var data = sheet.getDataRange().getValues();
  var urlCol = 1; // 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))]:'']);
  }
  sheet.getRange(2, urlCol, resultUrls.length).setValues(resultUrls);
// end edit response

// Email edit response URL
  var editURL = resultUrls[resultUrls.length - 1];
// END Email edit response URL

send email to users

MailApp.sendEmail(Email, subject, Here is the edit URL editURL

share|improve this answer
1  
It seems some formatting has been lost, or at least, I cannot make sense of the last two lines. Could you edit your answer so that it is clearer? It would also be useful if you could describe what the script does, piece by piece, so that others could modify your script for their own purposes. –  Vidar S. Ramdal Nov 18 '13 at 16:33
    
This is awesome! Thanks for posting. It works great and allows me to use this to track form posts and allow the user to go back if needed and edit. A simple vlookup in the spreadsheet and I can return the URL to a cell and create a simple HYPERLINK that allows for quick and dirty launching of the form edit from within a Google Doc. Thanks!!! –  user57294 Jan 19 at 1:22

Try This: (Credits is not for me, because i merge two solutions of the third part)

/* Send Confirmation Email with Google Forms */

function Initialize() {

    var triggers = ScriptApp.getScriptTriggers();

    for (var i in triggers) {
        ScriptApp.deleteTrigger(triggers[i]);
    }

    ScriptApp.newTrigger("SendConfirmationMail")
        .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
        .onFormSubmit()
        .create();

}

function SendConfirmationMail(e) {
  var form = FormApp.openById('***YOUR FORM CODE***');
    //enter form ID here

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('***SHEET NAME***');

    //Change the sheet name as appropriate
  var data = sheet.getDataRange().getValues();
  var urlCol = ***************COLUMN SEQUENCE EX 14******; // column number where URL's should be populated; A = 1, B = 2 etc
  var responses = form.getResponses();
  var timestamps = [], urls = [], resultUrls = [], url;

  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))]:'']);
    url = resultUrls[i-1]
  }
  sheet.getRange(2, urlCol, resultUrls.length).setValues(resultUrls);  

    try {

        var ss, cc, sendername, subject, headers;
        var message, value, textbody, sender;

        // This is your email address and you will be in the CC
        cc = Session.getActiveUser().getEmail();

        // This will show up as the sender's name
        sendername = "****YOUR NAME******";

        // Optional but change the following variable
        // to have a custom subject for Google Docs emails
        subject = "Registro de Oportunidade submetido com sucesso";

        // This is the body of the auto-reply
        message = "Nós recebemos seu registro de oportunidade.<br>Muito Obrigado!<br><br>";

        ss = SpreadsheetApp.getActiveSheet();
        headers = ss.getRange(1, 1, 1, ss.getLastColumn()).getValues()[0];

        // This is the submitter's email address
        sender = e.namedValues["********COLUMN NAME OF DESTINATION E-MAIL************"].toString();

        for (var i in headers) {

            value = e.namedValues[headers[i]].toString();

            // Do not send the timestamp and blank fields            
            if ((i !== "0") && (value !== "")) {
                message += headers[i] + ' :: ' + value + "<br>";
            }
        }

        message += "<br>Link to edit" + ' :: ' + url + "<br>";
        textbody = message.replace("<br>", "\n");

        GmailApp.sendEmail(sender, subject, textbody, 
                            {cc: cc, name: sendername, htmlBody: message});

    } catch (e) {
        Logger.log(e.toString());
    }

}
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.