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
- The spreadsheet owner is accountable for any impacts due to change of the spreadsheet content.
- The spreadsheet owner is responsible to inform the QV team before any format modification that could impact the scripts (See table below)
- 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
- 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 |
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.
The best way to get IT support is to use the new
Service One Platform.