Form filling ID in the spreadsheet

[Marcin Głuszak]

Hi guys

Please help, suggest how to find out which "row" is being edited by the form.

I have a form which, when saved, is processed by appsscript. The doc is filled in with the answers, then a pdf is generated and sent by e-mail. I managed to handle all this, although this is my first contact with scripts.

Because I want to give the possibility to edit the completed form, I would like to generate the pdf again after saving the changes and send it.

Unfortunately, here's a place I can't figure out:
After editing the form, only the corrected data are sent, the rest are empty.
Debugging { 'Time stamp': [ '2022-12-09 00:58:22' ],
   'Job description': [ '' ],
   'Student's name': [ '' ],
   'Degree': [ '1st' ],
   'To do': [ '' ],
   'Job title': [ '' ],...

How to extract the information which line in the answer sheet to manually download the remaining unchanged information to generate the pdf again?

Many thanks in advance for the guidance.
Marcin

[Phillip Bainbridge]

Hi Marcin

I might be slightly misunderstanding your query but I use the Google Response Sheet to capture the data entered into a Google Form for use elsewhere. The Event Object contains other things such as the row number that the Form submission is being written to. I have more detail and the code for scrutiny on my blog as an example.

Kind regards

Phil

[Marcin Głuszak]

Hi Phil

Thank you very much for your answer.
I looked at the site and what you show works for me.

Form -> send -> save to spreadsheet -> run script -> create doc -> create pdf -> send pdf->OK

The problem, however, is the next step if I let the filler correct his answers.

In brief:
Edit->Form -> send -> save changes to the spreadsheet (OK)-> run the script (only changed data)-> create doc (only changed data)-> create pdf -> send pdf->OK (file in 95% empty because it contains only changed data)

Full description:
When the form is filled out for the first time everything is ok and this is what the log looks like:
9 Dec 2022, 00:57:08 Debugging { 'Specialty': [ 'Engineering biomechanics' ],
   'Job title (English)': [ '123' ],
   'Promoter's name': ['321413214'],
   'Job title (Polish)': [ '123' ],
   'Language of work': [ 'Polish' ],
   'Promoter's email': [ 'te...@test.com' ],
   'Job Description': [ '12312' ],
   'Student's name': [ '123123' ],

but when the user wants to correct something, e.g. "Language of work", then after clicking send, 2 things happen, the corrected items in the sheet are corrected and everything is ok, but the change that he introduced is sent back to the script:

Dec 9, 2022, 00:59:38 Debugging { 'Specialty': [ '' ],
   'Job title (English)': [ '' ],
   'Promoter's name': [ '' ],
   'Job title (Polish)': [ '' ],
   'Language of work': [ 'English' ],
   'Promoter's email': [ '' ],

   'Job description': [ '' ],
   'Student's name': [ '' ],

and then the script generating the pdf makes it 95% empty because there is nothing to fill it.

That's why I'm looking for an idea how to download some answer ID to download the information that has not been corrected and therefore is empty.

The key word here is "form edited" :) and unfortunately I have not found anywhere that anyone encountered such a problem.

I hope I have now explained more clearly what my problem is.

Thanks again for looking into my problem.

greetings
Marcin

[Marcin Głuszak]

Just a little update.

I'm thinking of injecting myself into this cycle

Edit->Form -> send -> save changes to the spreadsheet (OK)-> run the script (only changed data)-> DOWNLOAD ID OF THE EDITED ANSWER-> DOWNLOAD OLD ANSWERS FROM SHEET-> create doc-> create pdf -> send pdf ->OK

[Phillip Bainbridge]

Hi Marcin

Ah I seeeeeee! Not something I've currently worked with - allowing people to edit their responses I'm afraid, but I completely get that this will throw everything off!!! I'll keep an eye out for anything that I spot online that may also help you out.

Kind regards

Phil

[cbmserv...@gmail.com]

Hi Marcin,

 

This is quite doable. But will require you to do some coding to achieve it.

 

I have similar code where someone submits a form and the data they enter is not correct, what I do is then grab the edit URL for the form and send that to them for them to update their form submission. Then when they resubmit, you will need to check what data has changed to understand the new additions.

 

Here is a function to get the edit url of the form submission:

/************************************************************************************************************

GetEditURL: Returns the URL to change a form entry based on formid and index given.

************************************************************************************************************/

function GetEditURL(index, formId)

{

  var form = FormApp.openById(formId); 

  var editURL = " ";

  var formSubmitted = form.getResponses(index);

  if(formSubmitted.length > 0)

  {

    editURL = formSubmitted[0].getEditResponseUrl();

  }

  else 

  {

    Logger.log("Failed to get Edit URL" + formId + " " + index)

  }; 

  return editURL;

  

};

 

The index passed in is the timestamp, usually its Column 1 of the Spreadsheet where the form submission is saved.

 

When looking at the spreadsheet, any value that was changed in the new form submission, is annotated. So just grab the notes for that range and if any of them are not blank, then they were updated. Here is how you grab the notes:

 

  var s = SpreadsheetApp.getActiveSheet();

  var dataRange = s.getRange(row,1,1, colEntered);

  var updates = dataRange.getNotes()[0];

 

So just go through updates array to see what position in the row was changed.

 

Good luck.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/97b1522b-3183-4460-b086-33ecc82e21fen%40googlegroups.com.