
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Google Sheets Append Rows To Sheet / Overwrite vs Insert not working
I'm working through my first Qlik Automation App and am super impressed at the ease of writing to Google Sheets. One block I am using (Append Rows To Sheet) has an Overwrite option to use rather than Insert Rows. I'm fairly certain at one point it was working properly and simply overwriting all the data on the google sheet which is what I am wanting. Now it will only append rows at the bottom rather than overwrite any rows. What am I missing? What I would like to do is completely overwrite any data on the sheet and replace it with new data. Solutions are welcome!!
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @DrB1
I have updated the automation and attached the workspace
Please give it a try and let me know the result
Thanks

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
It works!! It errored the first time because it needed the data matrix in the last block, so I reset that block to Data Matrix: List Variable and it is working!! Fantastic! This is great progress for me and I know it would not have happened without you! Thanks so much @AfeefaTk

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @DrB1
Could you please make sure you have the "OVERWRITE" option selected for the Insert Data Option Input field as shown below
If the "OVERWRITE" option is selected, the new data gets overwritten in place of existing data.
I hope this should give you the expected result.
Please let me know if you still have any further issues
Thanks!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you for responding @AfeefaTk . I already have Overwrite checked, but it still appends rows and adds the data at the bottom of existing data in the google sheet. It is not overwriting.
What could be causing it??

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I think you can see it here in the code:
Isn't it saying it's placing the data beginning at A15-F15?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @DrB1
We found that the behavior of overwrite and insert_rows option is as follows
With the overwrite it will search for empty rows that it can overwrite, whereas with the insert rows it inserts a new row whenever a new row is found.
So if the overwrite function is on and it does not find an empty row, it adds a new row to the sheet.
Please find screenshots
Google sheet before adding any rows:
I had a value in row A6:C6 before the append operation as shown above
Added ten rows with Overwrite option enabled and below is the result
As you can see, the rows A2:C5(empty row) got filled with incoming data, the A6:C6 row existing data got replaced with new data, and the A7:C11 row also got filled in with the rest of the incoming data
So if you have data in all the rows A1:C14, the OverWrite option is going to create a new row. This is expected as per the documentation which says adding data to the end of the sheet will still insert new rows or columns so the data can be written.
https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append
Hope this clarifies things at least
Thanks

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you for this information. My apologies for the delayed response. It sounds like I always need to keep an empty row beneath the header row. Let me try this and send you what I get.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Screenshot Google Sheet before Automation
After run:
Didn't overwrite existing data even with a blank row present.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @DrB1
I have tested it again and it works for me with a blank row present
Google sheet before adding rows
After adding ten rows with Overwrite option
Could you please share the screenshot of the per-block history of the 'Append Rows To Sheet' block in order to double-check the updatedRange in the output response
Something like this
Thanks

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
It fills in the empty row and then goes down below the data and adds data below the previous data rather than overwriting it.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
{
"spreadsheet_id": "1rfgfcKcLPmB4RinZ8ux2w36m1xuhF_0tQZIfVksja2A",
"sheetname": "PVCKids",
"data_matrix": {
"CODE": "4400",
"AccountName": "Kids Donations (Contra)",
"Actual": "($ 6117.68)",
"Annual Budget": "($ 4500.00)",
"% Annual Budget Used": "136%",
"Annual Budget Remaining": "$ 1617.68"
},
"insert_data_option": "OVERWRITE",
"blendr_on_error": "stop"
}
{
"spreadsheetId": "1rfgfcKcLPmB4RinZ8ux2w36m1xuhF_0tQZIfVksja2A",
"tableRange": "PVCKids!A1:F1",
"updates": {
"spreadsheetId": "1rfgfcKcLPmB4RinZ8ux2w36m1xuhF_0tQZIfVksja2A",
"updatedRange": "PVCKids!A2:F2",
"updatedRows": 1,
"updatedColumns": 6,
"updatedCells": 6
}
}

- « Previous Replies
-
- 1
- 2
- Next Replies »