I am using Google Apps script to detect new data record added (by Typeform) to the spreadsheet.
On the spreadsheet, only the event type = ‘On Change’ is triggered (not OnEdit).
While the Typeform writes all data for the survey (questions, fields, submitted at, token), only the questions data show up in the event source data object. The first 18 columns are questions, the rest are fields (hidden and custom), followed by Submitted At and Token.
Note that the spreadsheet has all columns populated and I can read with standard App script function on the sheet. However, I want to read the new/modified data as part of the event object.
Example:
The event object only shows the data
[[true, 4.0, 9.0, 7.0, 5.0, 2.0, 4.0, 5.0, , , 0.0, 3.0, , , Starting to figure it out]]
The sheet lastRow function shows all the columns
[true, 4.0, 9.0, 7.0, 5.0, 2.0, 4.0, 5.0, , , 0.0, 3.0, , , Starting to figure it out, , John, Doe, john.doe@acme.com, 55.0, 15.0, 0.0, 0.0, 0.0, 65.0, 60.0, 0.0, 20.0, 43.61, potter, 1.0, 9.0, 4.0, Mon Aug 26 21:27:05 GMT-04:00 2024, mo989p989oxgmo989pdwk1keiv6gnzkp]
Question: Does Typeform write a record to the sheet in two batches? First the questions and then then rest? If ‘yes’, why is the triggered fired only once? if ‘no’ then why does the event data object not have all the records?
Or Does Typeform write the first 15 columns and then the rest? Please note that the answer ‘Starting to figure it out’ appears in different column position in the data (same column in the spreadsheet)
Case 1:
Info [[true, 4.0, 9.0, 7.0, 5.0, 2.0, 4.0, 5.0, , , 0.0, 3.0, , , Starting to figure it out]]
Info [true, 4.0, 9.0, 7.0, 5.0, 2.0, 4.0, 5.0, , , 0.0, 3.0, , , Starting to figure it out, , John, Doe, john.doeatacme.com, 55.0, 15.0, 0.0, 0.0, 0.0, 65.0, 60.0, 0.0, 20.0, 43.61, potter, 1.0, 9.0, 4.0, Mon Aug 26 21:27:05 GMT-04:00 2024, mo989p989oxgmo989pdwk1keiv6gnzkp]
Case 2:
Info [[true, 4.0, 7.0, 5.0, 1.0, 9.0, 6.0, 3.0, , 5.0, 3.0, 8.0, , 9.0, 2.0]]
Info [true, 4.0, 7.0, 5.0, 1.0, 9.0, 6.0, 3.0, , 5.0, 3.0, 8.0, , 9.0, 2.0, , , Starting to figure it out, Charlotte, 2323, John, Doe, John.Doeatacme.com, 55.0, 55.0, 0.0, 0.0, 0.0, 53.33, 55.0, 30.0, 0.0, 90.0, 56.39, Tue Aug 27 00:27:57 GMT-04:00 2024, xhash5nvjmfh6cgxxixhashp6y8fbxcx]
