Google Apps SA Sheets Row Append Version 1

This handler uses the Google Sheets API ruby library to append values to a row on the bottom of a Google Sheets spreadsheet. Before using this handler, a valid Service Account must be created/used and the Google Sheets API needs to be enabled (see below for details on how to do this). Using the contents of a Google Service Account JSON key to authenticate against Google's API, the handler takes a spreadsheet id, worksheet name, and a comma separated list of values and updates the spreadsheet with the values (If you don't already have the spreadsheet id, it can be found in the URL when editing a spreadsheet in the browser - between the /d/ and /edit.) ie. For the url - https://docs.google.com/spreadsheets/d/1xyao84fV2LyU1svyx48KTSbBgiq_mPr2w142bbZce8g/edit#gid=0

• the id is: 1xyao84fV2LyU1svyx48KTSbBgiq_mPr2w142bbZce8g

serviceaccountkey_json Info Value

To authenticate with Google, the handler uses the contents to the Service Account JSON key to authenticate against Google. When the handler has been uploaded to Kinetic Task, open up the JSON key that was downloaded from Google (instructions below if needed) and copy the contents of it into the serviceaccountkey_json info value (copy everything, including the beginning and ending JSON braces {}).

Creating a Service Account

1. Navigate to the 'Google Developers Console'
   • Located at https://console.developers.google.com
   • If the URL has changed, try googling 'Google API Developer Console' to either find the console or find Google's current documentation for how to create an API project
2. Create a new API Project
   • If you have an existing project, navigate to that project page if you aren't already there by default
3. Navigate to Credentials
4. Create a new Credential of the type 'Service Account'
   • When creating the Service Account, choose a 'Key type' of JSON
5. Make note of where the JSON key was downloaded on creation. It will be needed for authentication later.

Enabling the Google Sheets API

1. In the Google Developers Console, navigate to the Library section
2. Find the Google Sheets API and click on the link
3. Find 'Enable' at the top of the page and click it to enable the API

Allowing an Impersonated User to Authenticate with the Service Account

NOTE: To allow an impersonated user to authenticate with a service account, a process called 'Domain-Wide Delegation of Authority' must be enacted by a Google Apps account admin to allow a Service Account to impersonate users.

1. Go to your Google Apps domain’s Admin console.

2. Go to the 'Security' page.

3. Select 'Advanced settings' from the list of options. If it isn't on the page originally, click 'Show More' at the bottom of the page.

4. Select 'Manage API client access' in the Authentication section.

5. Using the Client Id associated with your service account, attach 'One or More API scopes' for the services that you want the Service Account to have access to.

- API Scope(s) needed for this handler: 
                
                  - https://www.googleapis.com/auth/drive OR 
                
                  - https://www.googleapis.com/auth/spreadsheets 
                
                - If you don't have a Client Id associated with your Service Account (should see it under 'OAuth 2.0 client IDs' on the Credentials page), see the section below for setting that up 
                
                - If they aren't explicity added in every time the scope list is updated, any scopes that have been previously setup will be removed. 
                
                  - If you have a Client Id that already has domain wide calendar scope, to add the spreadsheets scope without losing the Calendar scope you need to add the Calendar scope to the comma separated list of scopes to add 
                
                    - https://www.googleapis.com/auth/calendar CHANGES TO 
                
                    - https://www.googleapis.com/auth/spreadsheets,https://www.googleapis.com/auth/calendar 
                

Creating a Client ID for a Service Account

1. In the Google Developers Console, navigate to the Credentials section
2. Navigate to Manage Service Accounts
3. Edit the Service Account you want to give a Client ID to
4. Select 'Enable G Suite Domain-Wide Delegation'
5. Back on the Credentials page, the Service Account will now be associated with a Client ID

Parameters

Sample Configuration

Results

This handler does not return any results.

Changelog

GoogleAppsSaSheetsRowAppendV1 (2017-07-10)

• Initial version. See README for details.

---

Related Handlers

Google Apps Group Add Users

This handler adds a list of existing users to the specified group.

Google Apps SA Calendar Create

This handler creates a new event in Google Calendar using a service account.

Google Apps SA Calendar Event Create

This handler creates a new Google Calendar event using a service account.

Google Apps SA Calendar Share

This handler shares a Google Calendar using a service account.

Google Apps SA Calendar Unshare

This handler unshares a Google Calendar using a service account.

Google Apps SA Group Add Owners

This handler sets a list of existing users to be managers of the specified group using a service account.

Google Apps SA Group Create

This handler creates a new Google Apps group using a service account.

Google Apps SA Group Delete

This handler deletes an existing Google Apps group using a service account.

Google Apps SA Group Remove Users

This handler removes a list of users from the specified group using a service account.

Google Apps SA Sheets Create

This handler creates a new Spreadsheet in Google Sheets.

Google Apps SA Sheets Delete

This handler deletes a Spreadsheet in Google Sheets.

Google Apps SA Sheets Rename

This handler renames a Spreadsheet in Google Sheets.

Google Apps SA Sheets Row Clear

This handler clears a row on a given Spreadsheet in Google Sheets.

Google Apps SA User Create

This handler creates a new user using a service account.

Google Apps SA User Delete

This handler deletes a user from the domain database using a service account.

Google Apps SA User Restore

This handler restores a user in the domain database using a service account.

Google Apps SA User Suspend

This handler suspends a user from the domain database using a service account.