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