topic Re: Google Sheets Append Rows To Sheet / Overwrite vs Insert not working in Qlik Automate

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

DrB1 — Wed, 18 Jan 2023 15:30:07 GMT

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

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

AfeefaTk — Thu, 19 Jan 2023 04:00:58 GMT

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!

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

DrB1 — Thu, 19 Jan 2023 14:34:09 GMT

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

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

DrB1 — Thu, 19 Jan 2023 14:39:50 GMT

I think you can see it here in the code:

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

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

AfeefaTk — Fri, 20 Jan 2023 05:43:41 GMT

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

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

DrB1 — Wed, 25 Jan 2023 16:28:28 GMT

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.

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

DrB1 — Wed, 25 Jan 2023 16:49:08 GMT

Screenshot Google Sheet before Automation

After run:

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

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

AfeefaTk — Fri, 27 Jan 2023 03:54:53 GMT

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

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

DrB1 — Fri, 27 Jan 2023 19:53:34 GMT

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

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

DrB1 — Fri, 27 Jan 2023 20:23:29 GMT

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

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

AfeefaTk — Mon, 30 Jan 2023 05:44:51 GMT

Hi @DrB1

Looking into the input and output of the block, it seems you are appending one single row at a time and not multiple rows

Is that correct?

The output response of "Append Rows to Sheet" block

This signifies that only one row is updated ("updatedRows": 1)

For the First run, it fills the empty row (A2:F2), and for the next runs it checks for empty row and gets added into that row (here it is A4:F4) which is expected

Could you please try appending multiple rows in a single run and see if that works?

Please refer to the example automation attached below

Thanks

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

DrB1 — Mon, 30 Jan 2023 13:17:42 GMT

I will be happy to try, but how? I’m using the Append Rows To Sheet block, and selecting a data item from a straight table. I’m not sure what makes it so 1 row or a block of rows?? Here's what it looks like. What do you suggest?

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

DrB1 — Mon, 30 Jan 2023 19:16:07 GMT

I'm not sure if this is how it switches from a single execution to 12 executions or if this is even related to whatever is messing it up. Here's a screenshot:

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

AfeefaTk — Tue, 31 Jan 2023 04:02:45 GMT

Hi @DrB1

I have created an example automation for your reference.

Please try importing automation to your workspace and see how it works.

More information on importing automations can be found here.

Thanks

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

DrB1 — Tue, 31 Jan 2023 13:36:18 GMT

Thank you for this example. I have imported it. In my previous attempts the order of automation was:

Set a bookmark on a Qlik Report
Get the straight table data from the Qlik Report
Append rows with the Qlik Report data to a Google Sheet
Goal ....to repeat this order through multiple cycles of bookmarks / Google Sheets

In your example, I see the Variable-listVariable block. I'm not sure what to do with it. Do I need to create variables? My variables would be the ones from the Qlik Report data. Do I need to create a list of the variables and then use the Get Straight Table Data to put into them?

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

AfeefaTk — Tue, 31 Jan 2023 14:12:58 GMT

Hi @DrB1

It would be easier to troubleshoot if you could share the automation workspace . I can take a look at your automation and then explain how to make it work for your workflow

Please follow the steps described here to export the automation

Thanks

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

DrB1 — Tue, 31 Jan 2023 14:19:23 GMT

See attached.

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

AfeefaTk — Tue, 31 Jan 2023 15:24:33 GMT

Hi @DrB1

I have updated the automation and attached the workspace

Please give it a try and let me know the result

Thanks

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

DrB1 — Tue, 31 Jan 2023 15:58:59 GMT

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