Getting Google Sheet to Work on Qlik Sense SaaS

[shansen]

I just wanted to post here to avoid people from wasting the time I did.

I was trying to load data from a Google Sheet into Qlik Sense SaaS.  I looked through the available list of connectors, and picked the one called "Google Drive Spreadsheets".  (This one should really be called, "Google Drive Metadata" to avoid users from thinking it is the one for Google Sheets.)  Of course, when I went in, all I could see was the ListFiles table that has metadata about all the objects in my Google Drive.

So, I messed around with that connector for a while, looking at on-line articles and seeing if there was some way to put something in the Query field that would allow it to access one of my Google Sheet files itself.  Every once in a while, I went & tried to connect using the "Google Drive" connector, but without success.  In my case this was failing (no spreadsheet files were visible), because the connector requires files to match one of the types in its File type menu, or they don't show up.  In other words, if you just create a Google Sheet & paste in some data, then go look for it to be visible in the Google Drive connector, you won't see it.  You have to save it as a type .XLSX or something before it will show up.

So, both the name of the Google  Drive Spreadsheets connector and the fact that there were no Google Sheet files visible through the Google Drive connector convinced me that the Google Drive Spreadsheets connector was the right one, so I wasted quite a bit of time researching on-line & trying to get that to work.  Qlik support finally straightened me out.  This is the right connector for Google Sheets in Qlik Sense SaaS: https://help.qlik.com/en-US/connectors/Subsystems/Web_Storage_Provider_Connectors_help/Content/Conne....  After changing the file type of my Google Sheets in my Google Drive, they showed up & I was able to load them.

I hope this post will save you some time.

[Albert_Candelario · Accepted solution]

Hello @shansen,

Thanks for sharing.

If there is any enhancement you would like to make to the product, kindly remember you always have the Ideation platform:

https://community.qlik.com/t5/Get-Started/Ideation-Guidelines-How-to-Submit-an-Idea/ta-p/1960234

Cheers,

Albert

[j_lai]

I can load data from Google Sheets using Google Drive & Spreadsheet connector in the Cloud environment. It is more or less like how we were using the web connector.

[shansen]

J_Iai,

I just checked again to see if by chance they had changed it based on my suggestion, but no.  In Qlik Sense SaaS, when I use the Google Drive & Spreadsheets connector I can only see specific system tables that tell you about the files & spreadsheets you have stored.  That connector does not allow access to data inside the spreadsheets.  Here is a screen shot.

Google Drive & Spreadsheets Connector Behavior

When I use the Google Drive Connector, that is when I can see and access the data in the files I have stored in my Google Drive folders, as long as they are the right file types.  Here is a screen shot:

Google Drive Connector Behavior

So, I don't see that the behavior has changed.

[j_lai]

Hi Shansen, 

you are very close.

1. First click ListFiles, you can preview the information about the availabale files in the folder selected. In the preview 's second column should be the file id. Copy the wanted sheet's id to your clipboard or txt file. 

2. Go to GetSheetValues by selecting it and uncheck ListFiles, then enter the info as requested in the window. Then preview the data.

3. Insert script button then will be activated and can be selected.  Script is generated.