Create and update time in google form responses

Question

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.

What about blocking the users from editing and force them to answer again the form and then on the sheet checking the answers by user? That way you will have the original and the update timestamp — Kessy, Jun 28 '21 at 08:54
That great idea; however, i have created form for call evaluation wherein there are 38 parameters that they need to fill in. On the next day or so they check their data and if any selection error or any other error is there they have to correct it — suraj, Jun 29 '21 at 06:21
And how about saving the created date, maybe on another sheet and then pairing it to the correct line with the new updated date? — Kessy, Jul 02 '21 at 14:34
Can you tell me how to save created date and pair it? The work around which came in my mind is to copy the last raw to another sheet on each form submit, so ty even if someone edit the response that will be also copied in another sheet. But here another sheet will have separate entries (i.e. the response which was submitted initially + the edited response). — suraj, Jul 03 '21 at 10:46
What do you mean by `Can you tell me how to save created date and pair it`? The workaround you mentioned would be what I suggested on the last comment and it can be done by using [Google Forms events](https://developers.google.com/apps-script/guides/triggers/events#google_forms_events), and yes that would mean that you will have in the same spreadsheet a sheet with the results, and another with the timestamp and I would use another one to pair both sheets. — Kessy, Jul 06 '21 at 15:39
@Kessy i think we are on the same page. Thanks for replying. — suraj, Jul 07 '21 at 07:01

score 0 · Answer 1 · answered Jun 28 '21 at 03:02

0

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.

How to check if current submission is editing response or a new response

answered Jun 28 '21 at 03:02

Rubén

34,714
9
70
166

Hi, I've created script and copy pasted in the original question, it is still not working. Using the script I have created it captures the time in the last column, but when I edit the form again it changes. I want to get the create date for this the time should not change after initial entry. Please check the code and help me with the correction. – suraj Jul 01 '21 at 08:26
please look into the code which I have pasted in original question box and let me know. – suraj Jul 03 '21 at 13:05

Create and update time in google form responses

1 Answers1