Google Spreadsheets for Qlikview

It is possible now to use Google Spreadsheets are data sources for Qlikview dashboards. This can be used to replace excel files (which need to be dropped manually in a network folder).

This has advantages : 

• You no longer need to have someone send the file each time to a shared folder - You simply update the google spreadsheet
• You can have more than one person editing the file.

But it also comes with restrictions :

• Like for excel files, you have to be very careful when editing. Many changes can break the loading to Qlikview.
• We are dependent on google maintenance of the spreadsheets tool.

Responsabilities

1. The spreadsheet owner is accountable for any impacts due to change of the spreadsheet content.
2. The spreadsheet owner is responsible to inform the QV team before any format modification that could impact the scripts (See table below)
3. Google doesn't inform us if they modify google spreadsheets - It's possible that some modifications break our scripts - In which case the QV team cannot be accountable for the issues (and we will work on solving it).

How it works

1 - Qlikview accesses and reads the file with user account sbs-is-appli-qlikview.support@solvay.com

• You need to give read access to this account.

2 - Qlikview doesn't use the names of the file & tabs - It uses internal ID's which are generated by Google (unique ID's).

• Once created you can safely change the file & tab names without impact (But it makes it more difficult for us to find where the information is)

3 - Qlikview doesn't use the header of your table.

• The names of the columns are fixed in the QV script based on the dashboard requirements (and can be different from the spreadsheet headers)

4 - New columns will not be automatcally added into Qlikview

1. • If you need to add a new column from your spreadsheet into QV, it has to be requested.

5 - Leave the A1 cell empty if not used by your data table/header

INCORRECT	CORRECT	CORRECT

6 - The first row should not have empty data cells

• Qlikview will consider that is the last row (even if there is data in other columns)

What you can and cannot do

Below is a list of what you can do without impacting the QV loadings and in which cases you need to inform the QV team before hand (so that we can modify the scripts)

What you cannot do (without informing us before)	What you can do
Remove access to the file from sbs-is-appli-qlikview.support@solvay.com	Rename the google spreadsheet (although not recommanded)
Delete the spreadsheet & recreate it (ID will be different even if you give the same name)	Rename the tab (although not recommanded)
Delete the tab and recreate it (ID will be different even if you give the same name)	Rename the columns of your table (columns are renamed in the QV script)
Change the order of the columns in the table or add new columns in between existing columns	Change the data values
Have an empty cell in the first column	Add new data rows
Add new header rows (Changing the line of the first data row)	Add formating : colors, text formats, borders
Give long column name with special character like '&', ';'. Example: "Date of Purchasing done ;"	Short and sweet unique name: Example: "Purchasing Date" or "PO_Date"

Recommendations

• Protect the rows which contains the table header, from editing to avoid issues :  https://support.google.com/docs/answer/144687?hl=en

• Limit the number of users that can edit the file.
• Remove unnecessary Column, if possible when is not required in the data extraction.