Solved: UpdateGoogleSpreadSheet in SaaS? - Qlik Community

Anders_Eriksson · ‎2022-08-22

In Sense OnPremise we used Qlik Web Connectors and GoogleDriveConnector.UpdateGoogleSpreadSheet
to export data to Google Drive Spread Sheets. Moving to SaaS we want to do the same thing.
But how can we update a Spread Sheet in Google Drive from an application in Qlik SaaS?
I don't see a GoogleDriveConnector in SaaS with this capability,
neither do I see any Application Automation blocks that can do it?

We need to export data to existing Google Drive Spread Sheets as those are used down the line.

Anders_Eriksson · ‎2022-08-25

Problem is we have hundreds of exports to Google Spreadsheets each day.
It is about 35 spreadsheets updated many times during the day.
On premise we could do this from the load script using QWC.
We are already up against the monthly limit for application automations.

Looked at automations but could not find a simple block to push a qvd/csv file to a Google Spreadsheet.
"Append Rows To Sheet" looks like it can push a whole table but I can't find how to get the table from a qvd/csv?
"Get Straight Table Data" can get a table from an app, but then I would need to build sheets with straight tables for all the exports. And we have som that are over 100 000 rows which is a limit of this block.

Through testing I discovered that a Google Spreadsheet can be updated from load script using the store command together with the Google Drive connector and specifying the spreadsheet name as the file name without a file extension. Only problem is the sheet name inside the spreadsheet will be the same as the filename. Unsure if the customer can accept this? Appears this was how QWC handled it also and then it would be okay.
There are data flows dependent on these Google Spreadsheets and certain things we are not allowed to change (like the spreadsheet ID).

View solution in original post

Vinay_B · ‎2022-08-25

Hi @Anders_Eriksson,

We have Google Drive & Spreadsheet connector through which you can import the data. However, you might not be able to write back to the spreadsheet in the app.

With regards to writing the data, you can also use Qlik Automation which has Google Spreadsheet through which you can write to Spreadsheet and also import the data from it. Please refer:

https://help.qlik.com/en-US/blendr/Content/blend-editor/google-sheets.htm

If this resolves your query, please click on "Accept as Solution" for confirmation. Thanks!

Anders_Eriksson · ‎2022-08-25

Problem is we have hundreds of exports to Google Spreadsheets each day.
It is about 35 spreadsheets updated many times during the day.
On premise we could do this from the load script using QWC.
We are already up against the monthly limit for application automations.

Looked at automations but could not find a simple block to push a qvd/csv file to a Google Spreadsheet.
"Append Rows To Sheet" looks like it can push a whole table but I can't find how to get the table from a qvd/csv?
"Get Straight Table Data" can get a table from an app, but then I would need to build sheets with straight tables for all the exports. And we have som that are over 100 000 rows which is a limit of this block.

Through testing I discovered that a Google Spreadsheet can be updated from load script using the store command together with the Google Drive connector and specifying the spreadsheet name as the file name without a file extension. Only problem is the sheet name inside the spreadsheet will be the same as the filename. Unsure if the customer can accept this? Appears this was how QWC handled it also and then it would be okay.
There are data flows dependent on these Google Spreadsheets and certain things we are not allowed to change (like the spreadsheet ID).

Anders_Eriksson · ‎2022-08-29

Why is this "accepted as solution"? I did not accept it and it does not solve my problem?
My own investigation is much closer to a solution and now undergoing final tests.