article Google Sheets - How to get started with Google Sheets in Automations in Official Support Articles

Google Sheets - How to get started with Google Sheets in Automations

Marius_Nuca — Wed, 23 Nov 2022 07:28:33 GMT

This article gives an overview of the available blocks in the Google Sheets connector in Qlik Application Automation. It will also go over some basic examples of creating a new spreadsheet, adding new sheets to it, adding some information to the sheet as well as removing information from it.

You will find the mentioned example attached to this article in JSON format so you can upload it to your workspace and get started with it easily.

This connector supports operations to manage spreadsheets, adding/removing sheets from spreadsheets, manipulating sheet data, and a few other support operations.

Authentication for this connector is based on the oAuth2 Protocol.

Let's start with the example prepared:

Create an automation;
From the left menu, select the Google Sheets connector;
Drag and drop the "Create Spreadsheet" block and fill in a title for the spreadsheet we will be adding
Let's go ahead and drag and drop the "Add Sheet to Spreadsheet" block and fill in the input parameters. (input parameter for the spreadsheet id can be filled manually by clicking the input and using the do lookup function for adding a new sheet to an existing spreadsheet or filled in from the output of the block before this one like shown in the picture)
Next step in our example was to append a couple of rows and columns to our newly added spreadsheet in one go by using the 'Append Rows to Sheet' block:

For text values you can encase them between double quotes and for number you can simply send them as they are. The end result should look like the following picture in you document:
Now in order to get the data from the spreadsheet we've used the block 'Get data from sheet with Headers' and the resulting output from that block will be a list object ( if there were multiple lines ) with the headers used as keys of the object and the values taken from the following lines.
Lastly we used the 'delete rows' block in order to delete 1 line from our sheet. Just as you can see on the block description, you are not allowed to delete all rows in a sheet.
In the presented JSON example, you will notice a disabled 'Delete Spreadsheet' block as well. That block is only available for accounts whose administrator has allowed the app beforehand in their G Suite administration panel, so not every user might have access to that blocks functionality. If you want to delete a spreadsheet and don't have the possibility from our Google Sheet connector, manually deleting the spreadsheets from your account is the only way to do it.

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.

Re: Google Sheets - How to get started with Google Sheets in Automations

Alejandro-juarez22 — Thu, 10 Nov 2022 17:00:51 GMT

Hello, is it possible to send the sheet by email?

Re: Google Sheets - How to get started with Google Sheets in Automations

AfeefaTk — Tue, 29 Nov 2022 04:51:06 GMT

Hi @Alejandro-juarez22

Unfortunately, It's not possible to send the google sheet as an attachment via email using QAA

We can achieve it using Microsoft Excel files where we can convert excel report to pdf and send it as an attachment using Send Mail block

For more info refer to the below article

https://community.qlik.com/t5/Official-Support-Articles/How-to-convert-Excel-reports-to-PDF-using-Qlik-Application/ta-p/2009048

Hope this helps!

Thanks!

Afeefa TK

Re: Google Sheets - How to get started with Google Sheets in Automations

AfeefaTk — Thu, 16 Feb 2023 04:26:27 GMT

Hi @Alejandro-juarez22

UPDATE

We found a way to send Microsoft excel report as an attachment using Send Mail block. Please take a look at this article.

Thanks

Re: Google Sheets - How to get started with Google Sheets in Automations

abhishek170 — Mon, 18 Sep 2023 12:12:28 GMT

Can We Link Google spreadsheets sales data into Qlik sense and automation?

Re: Google Sheets - How to get started with Google Sheets in Automations

abhishek170 — Tue, 19 Sep 2023 05:50:12 GMT

Can We Link Google Sheets to app and visualize with spreadsheets and after update i will change the data in google sheets and automatically change in the Qlik sense App