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:

  1. Create a google cloud project.

  2. In this project create a service account with permission to edit projects (write down the generated email address, you’ll need it later).

  3. 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.

  4. From the Google Cloud Dashboard select “Enabled APIs & services”, then select “Library”, scroll down and find “Google Sheets API”, and enable the API.

  5. 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.

ewogICJ0eXBlIjogInNlcnZpY2VfYWNjb3VudCIsCiAgInByb2plY3RfaWQiOiAiY2FwYWJsZS1yZXNwZWN0LTM3NDkwOSIsCiAgInByaXZhdGVfa2V5X2lkIjogImZkZjk0MGJjMzQ3...

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

1iPIvErhZNXFHLJTojr1UPf8xIAAHm3pP4gyxJCPOYak

sheet

Name of the sheet to be read

Sheet1

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

1iPIvErhZNXFHLJTojr1UPf8xIAAHm3pP4gyxJCPOYak

sheet

Name of the writing sheet

Sheet3

line_number

Line number to begin write to. Starts with 1. Can be used together with rownum variable to use incrementing numbers.

  • 3

  • ${rownum + 2}

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>

File Modified

File keys.json

2023-02-01 by Ivan Bečár