Table of Contents
1. Description
Driver for reading and writing of data to Google Sheets spreadsheet.
In order to use this driver a service user has to be created in Google Sheets with a generated key.json. This needs to be then Base64 encoded before used in a Graphlytic job. See below for more information.
2. Creating a Google Cloud service user with Google Sheets API enabled
Follow these steps to create a Google Cloud service user:
Create a google cloud project.
In this project create a service account with permission to edit projects (write down the generated email address, you’ll need it later).
Generate a key as a JSON file (see keys.json as an example) and encode the content using Base64 encoding (any online encoder can be used, such as https://www.base64encode.org/). The encoded string will be used as a service_user_key in Graphlytic jobs.
From the Google Cloud Dashboard select “Enabled APIs & services”, then select “Library”, scroll down and find “Google Sheets API”, and enable the API.
Share a spreadsheet with a service user’s email address (generated in step 2). Share with “editor” permission for write permissions.
3. Connection
Defines a connection to a single spreadsheet.
Parameters
Name | Description | Example |
---|---|---|
service_user_key | Base64 encoded generated key of a Google Sheets service user. |
|
Example
<properties>
key=ewogICJ0eXBlIjogInNlcnZpY2VfYWNjb3VudCIsCiAgInByb2plY3RfaWQiOiAic2FtcGxlLXByb2plY3QtaWQiLAogICJwcml2YXRlX2tleV9pZCI6ICJmZGY5NDBmNzk5NGEwNWQ3YTNiODM1YTZhOWU3OTE4MWJjMzQ0NjQ3IiwKICAicHJpdmF0ZV9rZXkiOiAiLS0tLS1CRUdJTiBQUklWQVRFIEtFWS0tLS0tXG5NSUlFdlFJQkFEQU5CZ2txaGtpRzl3eDZLXG5qbm5LdGxudHpzbVplYUFKYk51dWpmWjFkcXYveEtseTU1REJxRUIzd1p4WnNhc3J0Ukt5QnJJM1MwYVcrS3pxXG5Xalc2QzBSTTNJaVdYZlpUUVVKRkUxc2dTMmUrR0VQdUcxWGYzUDQwemJSWVdCQU9lNElxbnhVOUtRbHRMMExLXG56czIrcjdBV1h2S0ZVTXFEN1pEYlNWRThud0tCZ1FES2xvYStCOUhHeUFNa2U1QVBCcGZ2NXo5MjNnMGFuWHo0XG5ucDZQak1vWTZvN0hHcHhyQlVCUGhhWDlFY3djd0NlVENSYUJRNFJMQjFHb20yNGJyc01ZeFNCbWlOdnMvNmlpXG5hdU5GbWdSdlNiSUovZEoxMEJKampIS0RiQ0ZpVnZPbjlMV3pHMktPL0VCRmVVNEFiVWVXRGlaS2F4d1Baa1N5XG5ZbjlRVkNvWkN3S0JnQVN4T0xPak5WbzduNnRwRCtuSDB2WVg1R3VBdy9qQlcyOEM4cGhzeWFHaXh0VEtxL3ZLNzJ6N1grXG56S1NsTS81eC9FdGszOUpXWVJLR2I0cnRsSGRIYml0OCtaeTI0MncxR3dLQmdEWkxvNU9HTW8zQW9HQUMxOWRcbjJ3Y1hXS0JieE0yRzdjRUNBSDZyeW1GMnZOckFiRGlEVUMyb0hyUDBKZXN4Z3JSOXdya084MkR2WTJIS0Z1LzRcbnFuTCsxek5tK3g4V2xFbEFQSUM4YStVYzYzWUNvbk43cEttYnZCK1g5Z1dncmVwclRkbFJpRjBtbHAybytmRzdcbnpYMHBFRHZLNDl1R2JIRzJCZzl2U05nd2NCRGVwR0R4SXJKRzFyY0NnWUVBMWJEMFplV2k0ajhsT1NSdi8zQU5cblVFd1RJU3dyRnF1amhiNWREYk9aekkrVjhiSUlqaEFCa1Q5Z2Zkc05CVFdETjlUTDN3UEVjVGE2ZDdsNU9tYzJcbmRxQ0M2cTNra3BWSVFEY0lJSGw5a2d2eDhpNWxESUdEampnN0tGbjl0NStFRWRFd0twUGNwQUpScS9iMGc1cTM3WjhEVG5lWSszXG5KWDB6ZnlIVkFnTUJBQUVDZ2dFQURZaUxjN0luSkhKMzJzUUtXLzNJVTRsZkhyVkhNOVlUZzFBeDczOTdsNzk1XG5Ub1c3SlMzYUlMMFFyVm5aaGM1SGQrZzJkRklzMjJnQVdyT0llUE5ITXQ5OExLL1xuVnVtWjRZM3I5WjQvMkVzOUU1ZXY4ZE1tWnRXL1d5cWM2clIxMXRTZ2lRYURPeVhRTnN6Qm9VdDl6VEZkSTNKcU84VjdvR21yeU96XG4wMkZkSXBHUjlzbUlqdHJFdGdINjhBQ05LQStGTzJIVHpFaHN6bzgvUlIyNHZaOThVZ1ljT3F2SFFKNFRhTkZrXG5iLytSelJmbFB3RWVIK3B6S0MrVGdUd1JvUGNJNkpIQzVGTHc4UlBuTzZOZUk2dm5BNDRpVDBwY1RvODNNSnNpXG5lb3RzVEpUVTRzTEhvZFlyVnYwQkFRRUZBQVNDQktjd2dnU2pBZ0VBQW9JQkFRQ3VNeFZuKyt0UW1xMFRcbk9nM3QyeWtyaldVWGViK01CTEdPemlqMmNWWGRUMnQ1R2RvMVE3dVJVK0xkMWhhWjNPMjlOM05Lc0FreUlXTUFcbktFLy9aTko1bFJ1L0VnU2dGVzBLTE80U2NRWkFEVmFTOUFBS2t1VkNJUEJtMU5TZ1cyTDJnV3ZPNXlpREdDc1ZcbmhkVVBPOGlOQ2lqbDBIMWZUSWw2dEFKTWwxaytJK3N4MXl2MGxVNmtpbHlYdDlWUjNzZjY1Ukxpd3NzNTlrTHNcblMzUk1MN1JUNWhQUmgxc25hTTIrY0htSUt6bXdacGYzL0RrWkp1TGcxa0RxM24yU1JXZVh3OFFLVHZQelREUUZlZjVqK2Jtd2VDNVxuZEVxbndOK0hObkx6ckFOMDAvZEtkQzZva2xWY2lHRk5FTzN6NDFpWE16Tk42YUFLNFY3SmFcbnFORDEwQUZtSlNNQTlZNnBlVlBaUWtXa2UwdjFodkgxeUpMNUFjTG9VVGtnU01qUkRENVFwcHZTU0J3U2pSbWt5NzdxSkorclFXV1ZtNVdsazNMRHZiTDRnMTNaZHlGcUVuXG5rVmFKUUR3RDViSkhKNlNMZlNkbm5Hcz1cbi0tLS0tRU5EIFBSSVZBVEUgS0VZLS0tLS1cbiIsCiAgImNsaWVudF9lbWFpbCI6ICJzb21lLXVzZXJAc29tZS1kb21haW4uY29tIiwKICAiY2xpZW50X2lkIjogIjE1MjMwODcxNjExMDIxMTg1MTI5MCIsCiAgImF1dGhfdXJpIjogImh0dHBzOi8vYWNjb3VudHMuZ29vZ2xlLmNvbS9vL29hdXRoMi9hdXRoIiwKICAidG9rZW5fdXJpIjogImh0dHBzOi8vb2F1dGgyLmdvb2dsZWFwaXMuY29tL3Rva2VuIiwKICAiYXV0aF9wcm92aWRlcl94NTA5X2NlcnRfdXJsIjogImh0dHBzOi8vd3d3Lmdvb2dsZWFwaXMuY29tL29hdXRoMi92MS9jZXJ0cyIsCiAgImNsaWVudF94NTA5X2NlcnRfdXJsIjogImh0dHBzOi8vd3d3Lmdvb2dsZWFwaXMuY29tL3JvYm90L3YxL21ldGFkYXRhL3g1MDkvZ3JhcGhseXRpYy1zaGVldHMtYWNjb3VudCU0MHNvbWUtdXNlckBzb21lLWRvbWFpbi5jb20iCn0=
</properties>
<connection id=
"googleSheets"
driver=
"googleSheets"
>
service_user_key=$key
</connection>
4. Query
Reads from a spreadsheet. Provides values to be used in subsequent queries/scripts using this pattern:
$gsrow.<column>
Lines containing no value from column 0 to max_column are skipped.
Parameters
Name | Description | Example |
---|---|---|
spreadsheet_id | ID of a spreadsheet |
|
sheet | Name of the sheet to be read |
|
skip_rows | Number of lines to skip. If defined, reading of the file starts at line skip_rows+1. Default is 0 | 3 |
max_columns | Maximum number of columns to return. Default is 255 | 5 |
5. Script
Writes entries into a sheet of a spreadsheet identified by spreadsheet_id.
Parameters
Name | Description | Example |
---|---|---|
spreadsheet_id | ID of spreadsheet |
|
sheet | Name of the writing sheet |
|
line_number | Line number to begin write to. Starts with 1. Can be used together with rownum variable to use incrementing numbers. |
|
6. Examples
In this example, we will read data from “Sheet1” from columns A to E and write some of the data into “Sheet3” of the same spreadsheet.
<!DOCTYPE etl SYSTEM "
https://scriptella.org/dtd/etl.dtd
">
<
etl
>
<
properties
>
key=ewogICJ0eXBlIjogInNlcnZpY2VfYWNjb3VudCIsCiAgInByb2plY3RfaWQiOiAiY2FwYWJsZS1yZXNwZWN0LTM3NDkwOSIsCiAgInByaXZhdGVfa2V5X2lkIjogImZkZjk0MGJjMzQ3OTk0YTBmNmE5ZTc5MTgxNWQ3YTNiODM1YTQ2NDciLAogICJwcml2YXRlX2tleSI6ICItLS0tLUJFR0lOIFBSSVZBVEUgS0VZLS0tLS1cbk1JSUV2UUlCQURBTkJna3Foa2lHOXcwQkFRRUZBQVNDQktjd2dnU2pBZ0VBQW9JQkFRQ3VNeFZuKyt0UW1xMFRcbk9nM3QyeWtyaldVWGViK01CTEdPemlqMmNWWGRUMnQ1R2RvMVE3dVJVK0xkMWhhWjNPMjlOM05Lc0FreUlXTUFcbktFLy9aRTVldjhkTW1adFcvV3lxYzZyUjExdFNnaVFhRE95WFFOc3pCb1V0OXpURmRJM0pxTzhWN29HbXJ5T3pcbjAyRmRJcEdSOXNtSWp0ckV0Z0g2OEFDTktBK0ZPMkhUekVoc3pvOC9SUjI0dlo5OFVnWWNPcXZIUUo0VGFORmtcbmIvK1J6UmZsUHdFZUgrcHpLQytUZ1R3Um9QY0k2SkhDNUZMdzhSUG5PNk5lSTZ2bkE0NGlUMHBjVG84M01Kc2lcbmVvdHNUSlRVNHNMSG9kWXJWdkRJR0Rqamc3S0ZuOXQ1K0VFZEV3S3BQY3BBSlJxL2IwZzVxMzdaOERUbmVZKzNcbkpYMHpmeUhWQWdNQkFBRUNnZ0VBRFlpTGM3SW5KSEozMnNRS1cvM0lVNGxmSHJWSE05WVRnMUF4NzM5N2w3OTVcblRvVzdKYU0yK2NIbUlLem13WnBmMy9Ea1pKdUxnMWtEcTNuMlNSV2VYdzhRS1R2UHpURFFGZWY1aitibXdlQzVcbmRFcW53Tko1bFJ1L0VnU2dGVzBLTE80U2NRWkFEVmFTOUFBS2t1VkNJUEJtMU5TZ1cyTDJnV3ZPNXlpREdDc1ZcbmhkVVBPOGlOQ2lqbDBIMWZUSWw2dEFKTWwxaytJK3N4MXl2MGxVNmtpbHlYdDlWUjNzZjY1Ukxpd3NzNTlrTHNcblMzUk1MN1JUNWhQUmgxc25vN242dHBEK25IMHZZWDVHdUF3L2pCVzI4QzhwaHN5YUdpeHRUS3Evdks3Mno3WCtcbnpLU2xNLzV4L0V0azM5SldZUktHYjRydGxIZEhiaXQ4K1p5MjQydzFHd0tCZ1FEY0lJSGw5a2d2eDhpNWx4Nktcbmpubkt0bG50enNtWmVhQUpiTnV1amZaMWRxdi94S2x5NTVEQnFFQjN3Wnhac2FzcnRSS3lCckkzUzBhVytLenFcbldqVzZDMFJNM0lpV1hmWlRRVUpGRTFzZ1MyZStHRVB1RzFYZjNQNDB6YlJZV0JBT2U0SXFueFU5S1FsdEwwTEtcbnpzMityN0FXWHZLRlVNcUQ3WkRiU1ZFOG53S0JnUURLbG9hK0I5SEd5QU1rZTVBUEJwZnY1ejkyM2cwYW5YejRcbm5wNlBqTW9ZNm83SEdweHJCVUJQaGFYOUVjd2N3Q2VUQ1JhQlE0UkxCMUdvbTI0YnJzTVl4U0JtaU52cy82aWlcbmF1TkZtZ1J2U2JJSi9kSjEwQkpqakhLRGJDRmlWdk9uOUxXekcyS08vRUJGZVU0QWJVZVdEaVpLYXh3UFprU3lcblluOVFWQ29aQ3dLQmdBU3hPTE9qTlZTM2FJTDBRclZuWmhjNUhkK2cyZEZJczIyZ0FXck9JZVBOSE10OThMSy9cblZ1bVo0WTNyOVo0LzJFczlOK0hObkx6ckFOMDAvZEtkQzZva2xWY2lHRk5FTzN6NDFpWE16Tk42YUFLNFY3SmFcbnFORDEwQUZtSlNNQTlZNnBlVlBaUWtXa2UwdjFodkgxeUpMNUFjTG9VVGtnU01EWkxvNU9HTW8zQW9HQUMxOWRcbjJ3Y1hXS0JieE0yRzdjRUNBSDZyeW1GMnZOckFiRGlEVUMyb0hyUDBKZXN4Z3JSOXdya084MkR2WTJIS0Z1LzRcbnFuTCsxek5tK3g4V2xFbEFQSUM4YStVYzYzWUNvbk43cEttYnZCK1g5Z1dncmVwclRkbFJpRjBtbHAybytmRzdcbnpYMHBFRHZLNDl1R2JIRzJCZzl2U05nd2NCRGVwR0R4SXJKRzFyY0NnWUVBMWJEMFplV2k0ajhsT1NSdi8zQU5cblVFd1RJU3dyRnF1amhiNWREYk9aekkrVjhiSUlqaEFCa1Q5Z2Zkc05CVFdETjlUTDN3UEVjVGE2ZDdsNU9tYzJcbmRxQ0M2cTNra3BWSWpSREQ1UXBwdlNTQndTalJta3k3N3FKSityUVdXVm01V2xrM0xEdmJMNGcxM1pkeUZxRW5cbmtWYUpRRHdENWJKSEo2U0xmU2RubkdzPVxuLS0tLS1FTkQgUFJJVkFURSBLRVktLS0tLVxuIiwKICAiY2xpZW50X2VtYWlsIjogImdyYXBobHl0aWMtc2hlZXRzLWFjY291bnRAY2FwYWJsZS1yZXNwZWN0LTM3NDkwOS5pYW0uZ3NlcnZpY2VhY2NvdW50LmNvbSIsCiAgImNsaWVudF9pZCI6ICIxMTEwMjExNjkwMTg1MTI1MjMwODciLAogICJhdXRoX3VyaSI6ICJodHRwczovL2FjY291bnRzLmdvb2dsZS5jb20vby9vYXV0aDIvYXV0aCIsCiAgInRva2VuX3VyaSI6ICJodHRwczovL29hdXRoMi5nb29nbGVhcGlzLmNvbS90b2tlbiIsCiAgImF1dGhfcHJvdmlkZXJfeDUwOV9jZXJ0X3VybCI6ICJodHRwczovL3d3dy5nb29nbGVhcGlzLmNvbS9vYXV0aDIvdjEvY2VydHMiLAogICJjbGllbnRfeDUwOV9jZXJ0X3VybCI6ICJodHRwczovL3d3dy5nb29nbGVhcGlzLmNvbS9yb2JvdC92MS9tZXRhZGF0YS94NTA5L2dyYXBobHl0aWMtc2hlZXRzLWFjY291bnQlNDBjYXBhYmxlLXJlc3BlY3QtMzc0OTA5LmlhbS5nc2VydmljZWFjY291bnQuY29tIgp9
</
properties
>
<!-- Create a logger connection -->
<
connection
id
=
"logger"
driver
=
"log"
>
level=WARN
</
connection
>
<!-- Create Google Sheets connection -->
<
connection
id
=
"googleSheets"
driver
=
"googleSheets"
>
service_user_key=$key
</
connection
>
<!-- Use created Google Sheets connection to read data row-by-row -->
<
query
connection-id
=
"googleSheets"
>
spreadsheet_id=1iPIvErhZNXFHLJTojr1UPf8xIAAHm3pP4gyxJCPOYak
sheet=Sheet1
line_number=0
max_columns=5
<!-- For every row - write the row into another sheet with row offset +2 -->
<
script
connection-id
=
"googleSheets"
>
spreadsheet_id=1iPIvErhZNXFHLJTojr1UPf8xIAAHm3pP4gyxJCPOYak
sheet=Sheet3
line_number=${rownum + 2}
gsrow.B=$gsrow.A
gsrow.D=$gsrow.C
</
script
>
<!-- For every row - write first 4 columns in the log -->
<
script
connection-id
=
"logger"
>
$gsrow.A
$gsrow.B
$gsrow.C
$gsrow.D
</
script
>
</
query
>
</
etl
>