How to load data from Google Sheets - Qlik Sense?

[Anonymous]

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.

[Michael_Tarallo · Accepted solution]

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

[Anonymous]

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.

[Michael_Tarallo]

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

[Anonymous]

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?