Upload a CSV and then Append the values into google sheets as new rows


I would like to upload a CSV file to my app and then append the uploaded data into my Google Sheet database.

Steps i have completed.

  1. Can get the parsed value of the upload and display it in a table component.
  2. Can upload 1 row of data into google sheets, but i would like to upload all the rows.

Code for 1 row upload: [{"Collection":"{{ table1.selectedRow.data['0'].Collection }}"}]

Is there a way to bulk select all rows and then append to google sheet?

Hey @Klearner!

Do you run into any trouble if you try and upload the rows directly from your file input?

I imagine that, if you're passing the data to a table, {{ table1.data }} can also work in the "Values to append" field.

Curious to know what you run into when trying either method :slightly_smiling_face:

1 Like

Hi @Kabirdas ,

thank you! :slight_smile:

was able to append data directly from filebutton with {{fileButton1.parsedValue[0]}} function. Is there a way to prevent duplicate uploads etc if i have a unique field name. Etc SKU?

Thank you so much for your help!

@Klearner unfortunately we don't have built-in upserts with Google Sheets at the moment (this is largely due to the Sheets API itself not supporting them).

If you'd like to check for duplicates you'll need to pull the full column from your sheet and manually check for duplicates in the frontend. This can be doable if the number of rows you're checking against is relatively small.

You can try something like the following:

const skus = formatDataAsObject(yourSheetsQuery.data).SKU; 
const rowsToInsert = fileButton1.parsedValue[0].filter(row => !skus.includes(row.SKU)); 
yourInsertQuery.trigger({additionalScope: {inserts: rowsToInsert} });

The above uses additionalScope to also trigger the insert query but that isn't strictly necessary. You could have it just return the rows you want to pass to each query or a separate transformer to do the filtering. You can also pass the value directly to the insert query using something like this!

{{ fileButton1.parsedValue[0].filter(row => !formatDataAsObject(yourSheetsQuery.data).SKU.includes(row.SKU)) }}

Any way you choose, though, you'll need to pull those SKU values into Retool first.

Does that help?

Let me know if it raises any questions as well!