CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-forms
suraj
suraj

Reputation: 11

Create and update time in google form responses

Is there away to get google form (response) submit time and update time?

I have created google form and responses are being recorded in Google sheet. As soon as response is submitted it automatically captures time under first column that is "Timestamp". Now, if the response is updated ( for an example after three hours) then the time recorded initially (Timestamp) also gets updated. By the way i have created script which generates link to edit response in last column so that users can update their responses anytime. Is there any work around to get response submitted (created) time and update in different columns?

Any help is appreciated.

As suggested by Ruben,

I've created below script but it is not working

    function assignEditUrls() {
    var form = FormApp.openById('1UoHiwgl2Kw6RK5c7- 
    0kp1iFP0CPPR_LDbvSm1hw9xLg');
    var sheet = 
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form 
    responses 1');
    var data = sheet.getDataRange().getValues();
    var urlCol = 16;
    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);

    {
    var blankRow=sheet.getLastRow(); //identify the next blank row

    // date function to update the current date and time as 
    submittted on
    var value = sheet.getRange(blankRow, 19).setValue(new 
    Date()).setNumberFormat('dd-mm-yyyy h:mm:ss'); //Submitted On
    sheet.getSheetValues(value);
    }

    }

It is updating time in the last column, but if I update the form response then again it gets updated. I want create date which should be entered in the initial entry. Please help me with the correction in my script.

Upvotes: 0

Views: 3102

Answers (1)

Wicket
Wicket

Reputation: 38180

There is no built-in way to do that.

One option is that you use your script that adds the URL to edit the form response to also add the timestamp to another column when a new form response is submitted and to another column when the form response is edited.

Related

Upvotes: 1

Related Questions