Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

agdgn123
New Contributor III

How to load data from Google Sheets - Qlik Sense?

Hi Mike (mto ),

Is there a video available on how to connect QS desktop app to Google Sheets using a web connector? I am trying to do it based on the above instructions but it's not working for me and I don't know what I am doing wrong.

When creating the script in the web connector it says:

// IMPORTANT: If, when loading the above script, you receive a 'Script Error' Dialog box with a 'Field Not Found'

// (or other) error, the first thing you should do is copy and paste the full request URL (i.e. the URL between the square [...]

// brackets IN THE QLIKVIEW / QLIK SENSE SCRIPT ERROR DIALOG) into a browser address bar and check the response.

But it does not say what to do if the result is not successful (like in my case). I am getting the following response:

XML Parsing Error: not well-formed

Location: file:///C:/Users/adagan/AppData/Local/Temp/data-2

Line Number 1, Column 3007:.....

Any help would be much appreciated.

1 Solution

Accepted Solutions
Employee
Employee

Re: How to load data from Google Sheets - Qlik Sense?

Hi Anat - a few things to consider when accessing Google Sheets and to clarify what you mean by web connector. I am not sure what code you are referencing - but it sounds like something in the data load script from QVSource. Please read through this and let me know what options you are after.

Video #6 of this playlist may help:

Installing Qlik Web Connectors - YouTube

(1) Web File

I just tested a simple example with our standard Web FILE Connector that comes with Qlik Sense - NOTE the URL needs to be publicly accessible IF using the simple Web File Connector as you can't pass authentication.

Here is a sample from my Google Sheets  - note I needed to first publish it to the web as a .csv from within the Google Docs interface:

https://docs.google.com/spreadsheets/d/1Tw-jZxtTiSvLFHwPheQlbCx--Wv1Q0YB2pBeywqunMA/pub?gid=0&single...

---------------

NOW (2) - As per video

Since that original post - we have purchased QVSource and are integrating the connectors and repackaging them as our Qlik Web Connectors.

The Web Connectors package will work IF YOU NEED TO ACCESS a SECURE Sheet as you need to create an Oauth security token and authenticate against your account.

Google Drive & Spreadsheets Connector (this is a licenses product)

It is a bit more complicated process as you need to execute some additional steps to retrieve the IDs of the sheets you need etc - so you can use them as parameters in another step. The video shows this.

HTH

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

Regards,

Michael Tarallo (@mtarallo) | Twitter

Qlik

4 Replies
Employee
Employee

Re: How to load data from Google Sheets - Qlik Sense?

Hi Anat - a few things to consider when accessing Google Sheets and to clarify what you mean by web connector. I am not sure what code you are referencing - but it sounds like something in the data load script from QVSource. Please read through this and let me know what options you are after.

Video #6 of this playlist may help:

Installing Qlik Web Connectors - YouTube

(1) Web File

I just tested a simple example with our standard Web FILE Connector that comes with Qlik Sense - NOTE the URL needs to be publicly accessible IF using the simple Web File Connector as you can't pass authentication.

Here is a sample from my Google Sheets  - note I needed to first publish it to the web as a .csv from within the Google Docs interface:

https://docs.google.com/spreadsheets/d/1Tw-jZxtTiSvLFHwPheQlbCx--Wv1Q0YB2pBeywqunMA/pub?gid=0&single...

---------------

NOW (2) - As per video

Since that original post - we have purchased QVSource and are integrating the connectors and repackaging them as our Qlik Web Connectors.

The Web Connectors package will work IF YOU NEED TO ACCESS a SECURE Sheet as you need to create an Oauth security token and authenticate against your account.

Google Drive & Spreadsheets Connector (this is a licenses product)

It is a bit more complicated process as you need to execute some additional steps to retrieve the IDs of the sheets you need etc - so you can use them as parameters in another step. The video shows this.

HTH

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

Regards,

Michael Tarallo (@mtarallo) | Twitter

Qlik

agdgn123
New Contributor III

Re: How to load data from Google Sheets - Qlik Sense?

Thank you so much Mike!

I managed to connect an Excel Spreadsheet to the Qlik Sense App in both ways with your clear instructions above. Much appreciated!

This is the first step of a solution I am looking for whereby I can:

     1. Update my Excel source file on the go , meaning store it on a cloud such as Dropbox / Google Drive etc. so I can      update it  from my smartphone / tablet / laptop (I see now that it is possible thanks to your explanation above).

AND

     2. View the outcome of these "on the go" updates on my Qlik Sense app from mobile devices too.

In other words, be completely mobile - both on the input and the output.

Is that possible for the output part too?

If understand correctly the free version of Qlik Sense Cloud does not let us update the apps - once I upload an app to QS cloud , it stays "as is" even if I update the source file. Is that correct?

If so, is it possible to achieve my goal with any of the other QS products? how?

Thanks in advance!,

Anat.

Highlighted
Employee
Employee

Re: How to load data from Google Sheets - Qlik Sense?

Hi Anat - great to hear - you are correct - Qlik Sense Cloud Basic and Plus - do not have schedule data refreshes. Qlik Sense Cloud business does - BUT at this time, if your app is using FILES you still need to manually upload the updated data file to your group workspace data file area. I created a POC that uses Dropbox as a staging area where a Qlik Sense App in Qlik Sense Cloud Business can read the files from there and therefore any updates to the files on Dropbox would then be reflected when the Qlik Sense app refreshes. You can see this in action in this video here:

Update your Qlike Sense Cloud Business workgroup data files with REST and Dropbox - Proof of Concept

This technique uses the REST connector to access the file data on Dropbox, I would imagine Google Drive has a similar concept with their REST APIs.

HTH

Regards,

Mike T

Qlik

wayneko1
New Contributor

Re: How to load data from Google Sheets - Qlik Sense?

Hi Mike, It is useful. My question is how to get rid of the shared folder not in publicly. How to manage the user id and password during connection?