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 

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

{
"spreadsheetId": "1rfgfcKcLPmB4RinZ8ux2w36m1xuhF_0tQZIfVksja2A",
"tableRange": "PVCKids!A1:F1",
"updates": {
"spreadsheetId": "1rfgfcKcLPmB4RinZ8ux2w36m1xuhF_0tQZIfVksja2A",
"updatedRange": "PVCKids!A2:F2",
"updatedRows": 1,
"updatedColumns": 6,
"updatedCells": 6
}
}

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

[DrB1]

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?

[DrB1]

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:

[AfeefaTk]

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

[DrB1]

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

1. Set a bookmark on a Qlik Report
2. Get the straight table data from the Qlik Report
3. Append rows with the Qlik Report data to a Google Sheet
4. 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? 

[AfeefaTk]

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

[DrB1]

See attached.

[AfeefaTk · Accepted solution]

Hi @DrB1 

I have updated the automation and attached the workspace

Please give it a try and let me know the result

Thanks

[DrB1 · Accepted solution]

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