Google Sheets Actions
Google Sheets actoins are actions to interact with G Suite Google Sheets solution, an online and colaborative solution for spreadsheets.
After starting using these actions, it is important to have a Service Account and share your spreadsheet with the service account.
Actions
google.sheets.append
Append a new lines to the spreadsheet. If you set cell_range as A1, Marvin will take care to don't overright any data, and append the new content on the first blank line after A1, always inserting new data after the last line of spreadsheet.
Parameters:
spreadsheet - Spreadsheet variable returned by google.sheets.open
cell_range - range of cells where Marvin will start to append new data in Google Sheets. It could be single cell as A1, defining that all columns and rows shold be inserted after A1 cell, or could be a range A1:C1 new data will be inserted after this range.
line_values (optional) - array with each index been a different column, example:
grid_values (optional) - a grid where each line will represent a line and each line element will represent a column, example:
[
['Line 1 - Col1', 'Line 1 - Col2', 'Line 1 - Col3'],
['Line 2 - Col1', 'Line 2 - Col2', 'Line 2 - Col3']
]
Returns:
updated_range - range of cells that have been appended to spreadsheet.
first_col - identification of the first column appended in update_range.
first_row - identification of the first row appended in update_range.
last_col - identification of the last column appended in update_range.
last_row - identification of the last row appended in update_range.
Exceptions:
This action returns no Exception
google.sheets.delete_rows
Deletes the entire rows of between the start row number and the end row number.
Parameters:
spreadsheet - spreadsheet to be used for deleting rows.
start - number of the first row to be deleted.
end (optional) - number of the last row to be deleted. If just a single row need to be deleted, this parameter doesn't need to be informed.
Returns:
This action has no return.
Exceptions:
GoogleSheetsException - if any problem occurs during rows deletion.
google.sheets.get_cell
Return the value of a specific cell.
Parameters:
spreadsheet - Worksheet to be used for deleting rows.
cell_range - number of the first row to be deleted.
value_render_option (optional) - defines the type of insertion to spreadsheet (default=FORMATTED_VALUE). Further information could be found on this link
Returns:
updated_range - range of cells that have been appended to spreadsheet.
first_col - identification of the first column appended in update_range.
first_row - identification of the first row appended in update_range.
last_col - identification of the last column appended in update_range.
last_row - identification of the last row appended in update_range.
Exceptions:
GoogleSheetsException - if any problem occurs during rows deletion.
google.sheets.open
Open an online Google Spreadsheet to start using it.
To use any of Google integration solutions, is necessary to have a Google GCP service account. This is a JSON file used to authenticate and authorize you to use Google Sheet. For more information about how to create it, check this tutorial.
The image above shows how to get information need to pass as parameter on this action:
Parameters:
spreadsheet_id - ID of the spreadsheet to connect with. This ID could found on Spreadsheet URL.
Example:
URL: https://docs.google.com/spreadsheets/d/1_aF0LPBxZZ4vImNKAg8KjDNzb3rqx88-46eatuQmA0c/edit#gid=0
Spreadsheet ID: 1_aF0LPBxZZ4vImNKAg8KjDNzb3rqx88-46eatuQmA0c
current_sheet - name of the sheet to be opened as default, and can be found on the bottom of the Google Sheets screen.
service_account_path - path to JSON file with the service account authorization. For more information on how to create this credential, check this link
Returns:
spreadsheet - Spreadsheet variable to be used and passed to all other actions os Google Sheets actions.
Exceptions:
This action returns no Exception
google.sheets.update
Updates Google Sheets cells, updating cells to the given values
Parameters:
spreadsheet - Worksheet to be used for deleting rows.
cell_range - cell identification (A1 for example) to start updating values.
line_values (optional) - array with each index been a different column, example:
grid_values (optional) - a grid where each line will represent a line and each line element will represent a column, example:
[
['Line 1 - Col1', 'Line 1 - Col2', 'Line 1 - Col3'],
['Line 2 - Col1', 'Line 2 - Col2', 'Line 2 - Col3']
]
value_input_option (optional)- Determines how input data should be interpreted (default=RAW). Values could be:
| Value | Description |
|---|---|
| RAW | The values the user has entered will not be parsed and will be stored as-is |
| USER_ENTERED | The values will be parsed as if the user typed them into the UI. Numbers will stay as numbers, but strings may be converted to numbers, dates, etc. following the same rules that are applied when entering text into a cell via the Google Sheets UI |
Returns:
updated_range - range of cells that have been updated to spreadsheet.
first_col_updated - identification of the first column updated in update_range.
first_row_updated - identification of the first row updated in update_range.
last_col_updated - identification of the last column updated in update_range.
last_row_updated - identification of the last row updated in update_range.
Exceptions:
GoogleSheetsException - if any problem occurs during cells update.
Enable Google Sheets API and Create Service Account
To use the Google Sheets actions, first you'll need to create a service account
-
Access Google GCP console and select the project where you want to active the Google Sheets API and create the
-
Access Google Sheets API management and Enable API use
-
Click on Credentials
-
Click on Create Credentials and then on Service Account
-
Gives a name to your new Service Account and fullfill all asked informations and click DONE
-
The next two steps area only needed if you want finer control about this Service Account grants and accesses. If you don't need it, just click DONE on those steps.
-
After Service Account creation, on the Credentials screen, click on the created Service Account
-
Add a new Key to this Service Account, clicking on ADD KEY then Create new key
-
Select JSON option to create, click on CREATE and wait for the JSON file download.
-
Now you share your Google Spreadsheet with your Service Account. Don't forget to give privileges to edit the spreadsheet if you want Marvin to do so.
Share the Spreadsheet with Service Account
-
On your Google Sheets, click on Share
-
In Add people and groups insert your Service Account address
-
Check if the correct privileges are given to Service Account. If you want Marvin to change, delete or insert new data into your spreadsheet, you must set as Editor.
