Google Sheets Append Rows To Sheet / Overwrite vs Insert not working

[DrB1]

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!!

[AfeefaTk]

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!

[DrB1]

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??

[DrB1]

I think you can see it here in the code:

Isn't it saying it's placing the data beginning at A15-F15?

[AfeefaTk]

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

[DrB1]

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.

[DrB1]

Screenshot Google Sheet before Automation

After run:

Didn't overwrite existing data even with a blank row present.

[AfeefaTk]

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

[DrB1]

It fills in the empty row and then goes down below the data and adds data below the previous data rather than overwriting it.

[DrB1]

{
"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
}
}