A Simply Data Entry for QlikSense using Google Sheets

    Have you ever wanted to use Excel capabilities inside your Qlik app? In this document we show you how to use Google Sheets to deploy a data entry inside Qlik Sense Desktop app.


     

    First of all, we need to install the following extensions:


     


     

    You have to download them, unzip and copy both folders to your local path Documents\Qlik\Sense\Extensions.


     

    Once you have installed both extensions, open your Qlik Sense Desktop and create a new app. At this moment we need the spreadsheet we want to use as data entry. Go to https://www.google.com/intl/en_en/sheets/about/ and use your google account to create a new

    spreadsheet.


     


     


     

    Copy the URL of the sheet.


     

    Back to your Qlik Sense app, click on "Add Data" and then click on "File Web". Paste in the URL and type a Name for the connection:


     


     

    In the URL string you have to delete everything since the word edit to the end. Your URL will look like this:


     

    https://docs.google.com/spreadsheets/d/1SMLOZ-HJ809RUzSOGf9rpRVOa3OhnlIuAuXxJ4buEPw/edit#gid=0


     

    So you have to make it look like this:


     

    https://docs.google.com/spreadsheets/d/1SMLOZ-HJ809RUzSOGf9rpRVOa3OhnlIuAuXxJ4buEPw/


     

    This is due we have copied the URL from the edition mode of Google Sheet. A best way to identify or Sheet URL is going in the editor to the Menu "File" > "Share" then press the button "Get Shareable Link" and copy the URL is showed.


     


     


     

    Go forward and Load your Data.


     


     


     

    So what we have now is a data connection between Google Sheet and our Qlik Sense app.


     

    Continue editting a new sheet in your Qlik Sense app and drag&drop Media Box object into your sheet.


     


     


     


     

    In the object Properties, select in Media Box > Media Box Type: Web Site. Then in the Web Site Tab copy the Sheet URL (the full URL, with the edition path)


     

    https://docs.google.com/spreadsheets/d/1SMLOZ-HJ809RUzSOGf9rpRVOa3OhnlIuAuXxJ4buEPw/edit#gid=0


     


     

    And...Voila! You have your sheet in your app. Click on Done. Unfortunately life is not always days of wine and roses, and we have to face the problem that is not possible to edit if you are not logged with a google account. So we can find two possible workarounds:


     

    1. Manual Log In: In the embedded sheet click on Log In link, automatically opens a new tab in Qlik Sense. Introduce your account and password and close the tab. Close also the Qlik Sense app we have created (make sure you save your work before) and open again the Qlik Sense App in the hub. Now you can edit the sheet.
    2. Log In using Google SSO. Althought I have not test it, I think it is possible to login using the Google SSO. You could invoke it throught URL and setting a redirection. Something like this: 

      https://accounts.google.com/ServiceLogin?continue=https%3A%2F%2Fdocs.google.com%2Fspreadsheets%2Fd%2F1SMLOZ-HJ809RUzSOGf9rpRVOa3OhnlIuAuXxJ4buEPw%2F#identifier

      Notice that you have to encode firts the redirection URL.


     

    That's pretty cool, but it would be great if we make changes in your sheet and we could "apply" them. In fact, we could deploy this with doing a reload, but we could be more elegant if we create a button to launch a reload for us.


     

    Edit the Qlik Sense Sheet, drag and drop Reload Button Object:


     


     


     

    To test it, I added a KPI object containing the SUM of a field.


     


     


     

    Click on Done. And now we have a little trouble: if you try to edit your sheet

    To add new data to the sheet you have to log in google inside Qlik Sense. That's an ugly issue because when you clic on the Login button it opens a new tab inside Qlik Sense with the login form. Once you have logged, you have to close and press Reload.


     

    !
     


     

    Here you are! And that's all, folks. Hope you find it useful. Enjoy!