Posting multiple rows to Google Sheets

Queries and Resources
1

I have a Google Sheet that needs data from a Retool app. Single submissions are fine, but multiple submissions are inconsistent. Depending on execution speed, some rows are overwritten before moving to the next row. So, for example, if I have 9 rows of data to append, the query might append row 1 in GSheets, move to the next row, then append row 2 and then row 3 before it skips to the next row...etc, etc. This is the query, based on lots of forum searches and trial+error:

tblSearchResults.displayedData.forEach(row => qryPushToGSheetsMultiple.trigger({
  additionalScope : {
    REFERENCE_NUM:row.REFID,
    OWNER1FIRSTNAME:row.OWNER1FIRSTNAME,
    OWNER1LASTNAME:row.OWNER1LASTNAME,
    SITUSSTREETADDRESS:row.SITUSSTREETADDRESS,
    APNFORMATTED:row.APNFORMATTED,
    SITUSCITY:row.SITUSCITY,
    SITUSSTATE:row.SITUSSTATE,
    COUNTY:row.COUNTY,
    SITUSZIPCODE:row.SITUSZIPCODE,
    SITUSFULLADDRESS:row.SITUSFULLADDRESS,
    OFFER_PRICE:row.OFFER_PRICE,
    OFFER_DATE:row.OFFER_DATE,
    LOTACREAGE:row.LOTACREAGE,
    MAILINGFULLADDRESS:row.MAILINGFULLADDRESS,
    MUNICIPALITY:row.MUNICIPALITY,
    OPPORTUNITYZONE:row.OPPORTUNITYZONE,
    TOWNSHIP:row.TOWNSHIP,
    LATITUDE:row.LATITUDE,
    LONGITUDE:row.LONGITUDE,
    SUBDIVISION:row.SUBDIVISION,
    NEIGHBORHOOD:row.NEIGHBORHOOD,
    LEGALBOOK:row.LEGALBOOK,
    LEGALPAGE:row.LEGALPAGE,
    COUNTYLANDUSE:row.COUNTYLANDUSE,
    ZONING:row.ZONING,
    FLOODZONECODE:row.FLOODZONECODE,
    COMMUNITYNAME:row.COMMUNITYNAME,
    ASSESSEDTOTALVALUE:row.ASSESSEDTOTALVALUE,
    ASSESSEDLANDVALUE:row.ASSESSEDLANDVALUE,
    ASSESSEDIMPROVEMENTPERCENTAGE:row.ASSESSEDIMPROVEMENTPERCENTAGE,
    MARKETLANDVALUE:row.MARKETLANDVALUE,
    PROPERTYTAX:row.PROPERTYTAX,
    DELINQUENTTAXYR:row.DELINQUENTTAXYR,
    DELINQUENTTAXVALUE:row.DELINQUENTTAXVALUE,
    ELEMENTARYSCHOOL:row.ELEMENTARYSCHOOL,
    MIDDLESCHOOL:row.MIDDLESCHOOL,
    HIGHSCHOOL:row.HIGHSCHOOL,
    EXPIRATION_DATE:row.EXPIRATION_DATE
  }
  
}))

Ideally, I need the query that is fired here ( qryPushToGSheetsMultiple) to wait a bit before the loop repeats. I tried async/await but that's a hot mess. I am stuck.

2

Hi @fowlerjk, are you just appending or needing to update some of the rows as well with this query?
If it's the former, why aren't you using "Append data to spreadsheet" action of the native GSheet resource?

If it is the latter, have you explored using google sheet api?

3

Hi there! I'm having the same issue - I'm using workflow, and I'm using the native GSheet "Append data to a spreadsheet". For simplicity, I reduced the "values to append" down to the code below, and then I watched my sheet as 162 numbers overwrote themselves so much that I ended up with only 6 rows with numbers in them. Help!

[
{

"JobNumber":"{{value.JobNumber}}"

}
]

4

Hey folks!

Thanks for surfacing this issue, I've reported it to the dev team for investigation :slightly_smiling_face:

In the meantime, it looks like this may be caused by making concurrent requests to the Sheets API, can you try waiting for each call to finish before making the next? Something like:

for (let row of tblSearchResults.displayedData) {
  await qryPushToGSheetsMultiple.trigger({
    additionalScope: {
      /* ..etc. */
    },
  });
}

Or in the case of a workflows loop:

And let me know if that works?

(Note query5 and query7 should be replaced by the names of your own queries in the above screenshot)

1 Like
5

Perfect!! thank you!

6

Thank you! I think this bug is still active. In workflows i was trying to use a loop to append new rows to a google sheet. however, the result was the loop was inconsistently overwriting some of the same rows (i think race conditions) and this additional await fixed the issue. Much appreciated.

7

It is! Thanks for the bump, will pass it along to the team!

1 Like