🔗
Demo to preview the plugin:
🔗
Introduction
Empower your applications with the ability to seamlessly interact with Google Sheets using a service account. With over 35 actions and counting, this plugin offers extensive functionality to import, export, and format Sheets data, control conditional formatting, build and edit embedded charts, configure data validation, create and update named and protected ranges, manipulate pivot tables, and more.
This plugin provides developers with a powerful toolkit to integrate Google Sheets seamlessly into their applications, enabling efficient data management, visualization, and analysis.
Prerequisites
A service account is a special type of Google account that belongs to an application or a virtual machine (VM), rather than an individual user. It's used for authenticating and accessing Google Cloud Platform (GCP) services programmatically, typically in a server-to-server or application-to-service communication scenario.
Getting Setup
Register a Google Service Account with JWT (link) and obtain keys
Below are general steps for creating a service account and obtaining its keys on Google Cloud Platform (GCP):
- Navigate to the IAM & Admin page: Sign in to your Google Cloud Console . Then, go to the IAM & Admin page by clicking on the navigation menu and selecting IAM & Admin.
- Select "Service accounts": In the IAM & Admin page, select "Service accounts" from the left-hand menu.
- Create a new service account: Click on the "Create Service Account" button.
- Enter details: Provide a name and description for your service account. Optionally, you can specify a service account ID.
- Assign roles: Assign the necessary roles to your service account based on the permissions it requires. You can assign predefined roles or create custom roles.
- Create the service account: Click on the "Create" button to create the service account.
- Generate keys: After creating the service account, locate the newly created service account in the list and click on the three dots under "Actions". Then, select "Manage keys", then “Add key” → “Create new key” in the new opened tab.
- Choose key type: Choose the key type you want to create. JSON is a common choice.
- Download the key file: After selecting the key type, click on the "Create" button. This will generate the key file and download it to your computer. Keep this file secure as it grants access to your resources.
- Open the downloaded .json file and get from here two important values
a) “client email” account email value and fill with it the “lss” field of the plugin settings.
b) ”private key” and fill the “Private key” field in the “plugins” tab of your app.
- Before setting up the private key, be careful to format it from to have a proper private key form(more information here)
- Before using the plugin make sure to Enable Google Sheets on the link
- To have access too all the plugin functionalities and a better experience paste on the page plugin’s elements “Sheets Data Maker” and “Sheets Value Parser”.
Plugin Elements
Sheets Data Maker
Element States
Title | Description | Type |
Rows Output | Contains data of the spreadsheet rows that were created through the “Make Row Data” action. | Text |
Columns Output | Contains data of the spreadsheet rows that were created through the “Make Column Data” action. | Text |
Element Events
Title | Description |
Row Data is Ready | Triggered when the “Rows Output” state is filled with data and ready to be used. |
Column Data is Ready | Triggered when the “Columns Output” state is filled with data and ready to be used. |
Element Actions
Make Row Data
Makes row data ready to be used as a body request in the plugin api call “Write a Row (ROW TOOL)", which writes data to the spreadsheet, all details are created here in this action and returned in “Rows Output” plugin’s element state. After the data “Rows Output” in the plugin’s element is ready the “Row Data is Ready” event is triggered.
Fields:
Title | Description | Type |
Row Type | Represents the type of the provided data in the list. | App Type |
List Rows | The list with data needed. | As App Type |
Sheet Name | This parameter is a string that specifies the name of the target sheet within the Google Sheets spreadsheet where the rows are to be created. | Text |
From Row | This parameter is a string indicating the starting point of the range where the rows will be inserted. It likely represents the cell reference (e.g., "A1") from which the insertion will begin. | Text |
To Row | This parameter is a string indicating the ending point of the range where the rows will be inserted. | Text |
Make Column Data
Makes column data ready to be used as a body request in the plugin api call “Write a Column (COLUMN TOOL)", which writes data to the spreadsheet, all details are created here in this action and returned in “Columns Output” plugin’s element state. After the data “Columns Output” in the plugin’s element is ready the “Column Data is Ready” event is triggered.
Fields:
Title | Description | Type |
Column Type | Represents the type of the provided data in the list. | App Type |
List Columns | The list with data needed. | As App Type |
Sheet Name | This parameter is a string that specifies the name of the target sheet within the Google Sheets spreadsheet where the rows are to be created. | Text |
From Column | This parameter specifies the starting column from which data will be copied or manipulated. It represents the column reference (e.g., "A1") from which the operation will begin. | Text |
To Column | This parameter is a string indicating the ending point of the range where the columns will be inserted. | Text |
Sheets Value Parser
Element States
Title | Description | Type |
Output Date | A Date Value from the spreadsheet If the parsed data contains a single value | Date |
Output Date List | A List of Dates parsed | Date List |
Element Actions
Parse Date (05/30/2018 23:00:00)
You parse some dates as strings and get in return them as Dates format. If the parsed data contains a single value (e.g., a single cell in a spreadsheet), it publishes that value to the output state as 'Output Date'. If the parsed data contains multiple values (e.g., multiple cells in a spreadsheet), it publishes those values as an array under the output state 'Output Date List'.
Fields:
Title | Description | Type |
Is List? | A checkbox indicating whether the cell values are provided as a list (checked) or as a single value (unchecked).
| Checkbox |
Cell Value | (Optional) A string representing the value of a single cell in a spreadsheet. | Text |
Cell Value List | (Optional) A list of strings containing cell values from a spreadsheet | Text List |
Plugin API Calls
Create Spreadsheet (Action Type Call)
Allows users to create a new Google Sheets spreadsheet. By sending the appropriate parameters and authorization credentials, users can dynamically generate a blank spreadsheet, ready to be populated with data and shared collaboratively.
Fields:
Title | Description |
Title | Name of the spreadsheet. |
💡
To have access from Google Sheets to the created Spreadsheet ,you have to first give access to the document through “Allow Spreadsheet Access” api calls, choose the one that better suits your needs (anyone, user).
For opening the spreadsheet link you can use: https://docs.google.com/spreadsheets/d/
Paste here the spreadsheet id/edit#gid=0Get Spreadsheet (Action Type Call)
Retrieves information about a specific Google Sheets spreadsheet identified by its unique ID.
Fields:
Title | Description |
/SpreadSheet ID | Unique identifier for the target spreadsheet. |
includeGridData | This parameter is a boolean value that determines whether the response should include grid data. If set to true, the response will include a grid representation of the spreadsheet's sheets. This includes the actual data in each cell, as well as formatting information such as font size, background color, etc. If set to false, the grid data will be excluded from the response, resulting in a smaller payload size. |
ranges | (Optional) This parameter allows you to specify which specific ranges of cells you want to retrieve data for. It accepts one or more range strings separated by commas. Each range string specifies a rectangular block of cells in the format "sheet_name!top_left_cell:bottom_right_cell". For example, "Sheet1!A1:B5" specifies the range from cell A1 to B5 in the sheet named "Sheet1". Providing specific ranges allows you to fetch only the data you need, which can be more efficient than fetching the entire spreadsheet. |
Get Spreadsheet (Data Type Call)
Retrieves information about a specific Google Sheets spreadsheet identified by its unique ID.
Fields:
Title | Description |
/SpreadSheet ID | Unique identifier for the target spreadsheet. |
hash | Unique number to make a unique request. Is used for the retrieved data always to be up to date, for it to work right the field value should always be different, best practice is to use the Current date Unix. |
includeGridData | This parameter is a boolean value that determines whether the response should include grid data. If set to true, the response will include a grid representation of the spreadsheet's sheets. This includes the actual data in each cell, as well as formatting information such as font size, background color, etc. If set to false, the grid data will be excluded from the response, resulting in a smaller payload size. |
ranges | (Optional) This parameter allows you to specify which specific ranges of cells you want to retrieve data for. It accepts one or more range strings separated by commas. Each range string specifies a rectangular block of cells in the format "sheet_name!top_left_cell:bottom_right_cell". For example, "Sheet1!A1:B5" specifies the range from cell A1 to B5 in the sheet named "Sheet1". Providing specific ranges allows you to fetch only the data you need, which can be more efficient than fetching the entire spreadsheet. |
Get Spreadsheet Properties (Data Type Call)
Retrieves only properties information about a specific Google Sheets spreadsheet identified by its unique ID.
The response will only include the properties of each sheet, such as title, index, and visibility, rather than the full content of the sheets. This enables efficient retrieval of sheet metadata, which can be useful for various applications, including managing and organizing spreadsheet data programmatically.
Fields:
Title | Description |
/Spreadsheet ID | Unique identifier for the target spreadsheet. |
Get Spreadsheet Properties (Action Type Call)
Retrieves only properties information about a specific Google Sheets spreadsheet identified by its unique ID.
The response will only include the properties of each sheet, such as title, index, and visibility, rather than the full content of the sheets. This enables efficient retrieval of sheet metadata, which can be useful for various applications, including managing and organizing spreadsheet data programmatically.
Fields:
Title | Description |
/Spreadsheet ID | Unique identifier for the target spreadsheet. |
Get Sheet Values (Range) (Data Type Call)
Retrieves the data from a specified range of cells within a Google Sheets spreadsheet identified by its unique ID. By providing the appropriate authorization credentials and specifying the range of cells, users can access the values stored in those cells directly. The “hash” parameter indicate a specific sheet within the spreadsheet for targeted data retrieval.
Fields:
Title | Description |
/Spreadsheet ID | Unique identifier for the target spreadsheet. |
/range | Specifies the range of cells from which you want to retrieve data. For example, "Sheet1!A1:B5" specifies the range from cell A1 to B5 in the sheet named "Sheet1". |
hash | Identifier for the specific sheet within the spreadsheet. This is typically provided in the URL for direct access to a specific sheet. |
Get Sheet Values (Column) (Data Type Call)
Get data from a specified range of cells within a Google Sheets spreadsheet that data should be retrieved column-wise.
Column Wise:
💡
When data is retrieved "column-wise" from a spreadsheet, it means that the data is organized and returned in a manner where each column is represented as a separate entity or set of values.
Ex:
A B C
1 4 7
2 5 8
3 6 9
→
[
[1, 2, 3],
[4, 5, 6],
[7, 8, 9]
]
Fields:
Title | Description |
/Spreadsheet ID | Unique identifier for the target spreadsheet. |
range | Specifies the range of cells from which you want to retrieve data. For example, "Sheet1!A1:B5" specifies the range from cell A1 to B5 in the sheet named "Sheet1". |
hash | Identifier for the specific sheet within the spreadsheet. This is typically provided in the URL for direct access to a specific sheet. |
Get Sheet Values (Formula) (Data Type Call)
Retrieves data from a specified range of cells within a Google Sheets spreadsheet, with the option to render formulas instead of their evaluated results. This call specifies that formulas should be returned as they are written in the spreadsheet.
Fields:
Title | Description |
/Spreadsheet ID | Unique identifier for the target spreadsheet. |
/range | Specifies the range of cells from which you want to retrieve data. For example, "Sheet1!A1:B5" specifies the range from cell A1 to B5 in the sheet named "Sheet1". |
hash | Identifier for the specific sheet within the spreadsheet. This is typically provided in the URL for direct access to a specific sheet. |
Get Sheet Values (Formatted Value) (Data Type Call)
Fetches data from a specified range of cells within a Google Sheets spreadsheet, returning the formatted values of those cells. This allows retrieving human-readable representations of data, including any applied formatting such as dates, currencies, percentages, etc. The response provides the formatted values, enabling seamless integration with external systems that require data in a readable format.
💡
To obtain formatted values in the response, you would typically apply formatting to the cells in your Google Sheets spreadsheet.
Fields:
Title | Description |
/Spreadsheet ID | Unique identifier for the target spreadsheet. |
/range | Specifies the range of cells from which you want to retrieve data. For example, "Sheet1!A1:B5" specifies the range from cell A1 to B5 in the sheet named "Sheet1". |
hash | Unique number to make a unique request. Is used for the retrieved data always to be up to date, for it to work right the field value should always be different, best practice is to use the Current date Unix. |
Get Sheet Values (Row) (Data Type Call)
This request fetches data from a specific range of cells within a Google Sheets spreadsheet. It arranges the data in row-major order, meaning each inner array represents a row of values. This is useful for accessing and processing spreadsheet data row by row.
Fields:
Title | Description |
/Spreadsheet ID | Unique identifier for the target spreadsheet. |
range | Specifies the range of cells from which you want to retrieve data. For example, "Sheet1!A1:B5" specifies the range from cell A1 to B5 in the sheet named "Sheet1". |
hash | Identifier for the specific sheet within the spreadsheet. This is typically provided in the URL for direct access to a specific sheet. |
Add Sheet to Spreadsheet (Action Type Call)
Adds a new sheet to a Google Sheets spreadsheet.
Fields:
Title | Description |
/Spreadsheet ID | Unique identifier for the target spreadsheet. |
Sheet Title | Specifies the title of the new sheet. |
Columns | This parameter denotes the number of columns in the grid of the new sheet. |
Rows | This parameter indicates the number of rows in the grid of the new sheet. |
Sheet Position | This parameter determines the position of the new sheet within the spreadsheet. The index is zero-based, meaning the first sheet has an index of 0, the second sheet has an index of 1, and so on.
When adding or updating sheet properties, if this field is excluded then the sheet is added or moved to the end of the sheet list. |
Remove Sheet from Spreadsheet (Action Type Call)
Allows you to programmatically delete a specific sheet from a Google Sheets spreadsheet.
Fields:
Title | Description |
/Spreadsheet ID | Unique identifier for the target spreadsheet. |
Sheet ID | Identifies the ID of the sheet to be deleted. |
Clear Ranges (Action Type Call)
Clears the values from specified ranges within a Google Sheets spreadsheet.
Fields:
Title | Description |
/Spreadsheet ID | Unique identifier for the target spreadsheet. |
Ranges to Clear | This parameter indicates the cell ranges to be cleared "SheetName!StartCell:EndCell". |
Create Spreadsheet (Custom) (Action Type Call)
Allows creating Spreadsheet in more flexible, customed way by using information from google docs create method and spreadsheet request body.
Fields:
Title | Description |
body | Request body typically contains the properties of the new spreadsheet, such as its title, sheets, formatting, etc. But because it is a customed action, you decide what information should have your new spreadsheet but in a JSON form.
Ex:
{
"properties": {
"title": "myCustomSheet",
"defaultFormat": {
"backgroundColor": {
"alpha": 1,
"blue": 12,
"green": 22,
"red": 30
},
"horizontalAlignment": "CENTER",
"borders": {
"bottom": {
"color": {
"alpha": 12,
"blue": 45,
"green": 121,
"red": 41
},
"style": "DASHED",
"width": 122
},
"left": {
"color": {
"alpha": 1,
"blue": 121,
"green": 200,
"red": 50
},
"style": "DASHED",
"width": 100
},
"right": {
"color": {
"alpha": 2,
"blue": 121,
"green": 121,
"red": 0
},
"style": "DASHED",
"width": 140
},
"top": {
"color": {
"blue": 0,
"alpha": 0,
"green": 0,
"red": 0
},
"style": "DASHED",
"width": 0
}
},
"hyperlinkDisplayType": "PLAIN_TEXT",
"textFormat": {
"bold": false,
"fontSize": 0,
"foregroundColor": {
"alpha": 0,
"blue": 0,
"green": 0
}
}
}
}
} |
Update a Spreadsheet (Custom) (Action Type Call)
Allows updating Spreadsheet in more flexible, customed way by using information from google docs update method and its request body.
Fields:
Title | Description |
/SpreadsheetID | A unique identifier assigned to each Google Sheets spreadsheet. |
body | The JSON representation to be sent for spreadsheet update.
Please check its structure here. |
Add Charts to Spreadsheet (Custom) (Action Type Call)
Adding charts to spreadsheets means incorporating visual representations of data within the cells of a Google Sheets document. These charts help users to comprehend complex datasets more easily by presenting them in a graphical format.
Fields:
Title | Description |
/SpreadsheetID | A unique identifier assigned to each Google Sheets spreadsheet. |
body | The JSON representation to be sent for adding charts to spreadsheet .
Please check its structure here. |
Delete Chart From Spreadsheet (Custom) (Action Type Call)
Allows you do delete the added chart.
Fields:
Title | Description |
/SpreadsheetID | A unique identifier assigned to each Google Sheets spreadsheet. |
Chart ID | A Chart ID is a unique identifier assigned to each chart object within a Google Sheets spreadsheet. |
Add Protected Range (Action Type Call)
Allows for the programmatically enforced protection of specific cell ranges within a Google Sheets spreadsheet.
Fields:
Title | Description |
/SpreadsheetID | A unique identifier assigned to each Google Sheets spreadsheet. |
Sheet ID | This parameter specifies the ID of the sheet where the protected range will be applied. |
Start Column | This parameter indicates the starting column index of the protected range. |
End Column | This parameter indicates the ending column index of the protected range. |
Start Row | This parameter indicates the starting row index of the protected range. |
End Row | This parameter indicates the ending row index of the protected range. |
Description | an optional field that allows you to provide additional context or information about the protected range being added to the spreadsheet.
While the description itself does not directly appear anywhere within the spreadsheet interface, it can be retrieved programmatically through the API. |
Warning Only | If set to "TRUE" - a warning will be prompted when editing. It is possible to anyone to edit this protected range, still, the "confirm the edit" warning will be shown.
If set to "FALSE" - it will be impossible to edit this field for anyone (except for the creator). If the editor is not specified, then any editor in the document will be able to edit this range. |
Conditional Formatting Spreadsheet (Custom) (Action Type Call)
Conditional formatting allows users to automatically format cells based on certain conditions or criteria, such as values, text, or dates. This feature is commonly used to highlight important data, visualize trends, or identify outliers within a dataset.
Fields:
Title | Description |
/SpreadsheetID | A unique identifier assigned to each Google Sheets spreadsheet. |
body | The request body to be sent.
Please check its structure here. |
Data Validation Spreadsheet (Action Type Call)
This request enables programmatically setting data validation rules for specific cell ranges in a Google Sheets spreadsheet, ensuring data integrity and consistency within the spreadsheet.
Fields:
Title | Description |
/SpreadsheetID | A unique identifier assigned to each Google Sheets spreadsheet. |
Sheet ID | Identifies the sheet where data validation rules will be applied. |
Start Row Number | Specifies the starting row index of the range to apply data validation. |
End Row Number | Specifies the ending row index of the range to apply data validation. |
Start Column Index | Indicates the starting column index of the range to apply data validation. |
End Column Index | Indicates the ending column index of the range to apply data validation. |
Condition Type | This is the data validation rule to set on each cell in the range. Leave unspecified, to reset the data validation in the range. This is the condition that data in the cell must match - https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/other#ConditionType |
User Entered Value | The values of the condition. The number of supported values depends on the condition type . Some support zero values, others one or two values, and ConditionType.ONE_OF_LIST supports an arbitrary number of values. A value the condition is based on. The value is parsed as if the user typed into a cell. Formulas are supported (and must begin with an = or a '+'). |
Input Message | A message to show the user when adding data to the cell. |
Use Strict? | True if invalid data should be rejected. |
Move/Resize Chart From Spreadsheet (Custom) (Action Type Call)
This request enables the programmatically controlled adjustment of the position and size of embedded objects, enhancing the layout and presentation of data within the Google Sheets spreadsheet.
Fields:
Title | Description |
/SpreadsheetID | A unique identifier assigned to each Google Sheets spreadsheet. |
Chart ID | Identifies the specific chart or embedded object to be repositioned within the spreadsheet. |
Row Number | Specifies the row index of the anchor cell for positioning the chart. |
Column Number | Specifies the column index of the anchor cell for positioning the chart. |
X Offset in px | Indicates the horizontal offset, in pixels, of the chart relative to the anchor cell. |
Width in px | Defines the width, in pixels, of the chart. |
Height in px | Defines the height, in pixels, of the chart. |
Read Charts on Spreadsheet (Custom) (Data Type Call)
Retrieving chart data that is present within a Google Sheets spreadsheet.
Fields:
Title | Description |
/SpreadsheetID | A unique identifier assigned to each Google Sheets spreadsheet. |
Style Sheet Header (Action Type Call)
Styles the header cells in a way that improves the readability and usability of the spreadsheet.
Fields:
Title | Description |
/SpreadsheetID | A unique identifier assigned to each Google Sheets spreadsheet. |
Sheet ID | Identifies the sheet where the styling will be applied. |
Text Alignment | Specifies the horizontal alignment of the text within the cells (e.g., "LEFT", "CENTER", "RIGHT"). |
Background Red Color | Defines the intensity of the red component for the background color of the cells. |
Background Green Color | Defines the intensity of the green component for the background color of the cells. |
Background Blue Color | Defines the intensity of the blue component for the background color of the cells. |
Text Red Color | Defines the intensity of the red component for the text color of the cells. |
Text Green Color | Defines the intensity of the green component for the text color of the cells. |
Text Blue Color | Defines the intensity of the blue component for the text color of the cells. |
Font Size | Specifies the size of the font used for the text within the cells. |
SheetID | Identifies the sheet where the styling will be applied. |
is Bold? | Indicates whether the text within the cells should be displayed in bold format (true/false). |
Copy Sheet to Another (Action Type Call)
Enables the programmatically-controlled duplication of sheets within Google Sheets spreadsheets, providing flexibility in managing and organizing spreadsheet data.
Fields:
Title | Description |
/SpreadsheetID | A unique identifier assigned to each Google Sheets spreadsheet. |
Sheet ID | Identifies the sheet where the data is taken from. |
Destination Spreadsheet ID | Spreadsheet ID where the sheet is copied. |
Add Empty Rows to Sheet (Action Type Call)
Enables the insertion of rows into a specific sheet of a Google Sheets spreadsheet, providing a way to dynamically adjust the sheet's structure and accommodate additional data.
Fields:
Title | Description |
/SpreadsheetID | A unique identifier assigned to each Google Sheets spreadsheet. |
Sheet ID | Identifies the sheet where rows will be inserted. |
Start From Row | Specifies the row index where the insertion will begin. |
End at Row | Indicates the row index where the insertion will end. |
Add Empty Columns to Sheet (Action Type Call)
Inserts columns into a specific sheet within a Google Sheets spreadsheet.
Fields:
Title | Description |
/SpreadsheetID | A unique identifier assigned to each Google Sheets spreadsheet. |
Sheet ID | Identifies the sheet where columns will be inserted. |
Start From Column | Specifies the column index where the insertion will begin. |
End at Column | Indicates the column index where the insertion will end. |
Write to a Cell (Action Type Call)
Inserts data into a spreadsheet cell.
Fields:
Title | Description |
/SpreadsheetID | A unique identifier assigned to each Google Sheets spreadsheet. |
Value | Represents the new value or content that you want to insert into the specified cell. |
Cell | Specifies the cell address where the new value will be inserted. This is typically represented in A1 notation (e.g., "A1", "B2"). |
Sheet Name | Indicates the name of the sheet within the spreadsheet where the specified cell is located. This parameter allows you to target a specific sheet when updating cell values in a multi-sheet spreadsheet. |
Write a Column Sheet (Action Type Call)
This request is aimed at updating the values within a specified range in a Google Sheets spreadsheet. The values are arranged row-wise.
Fields:
Title | Description |
/SpreadsheetID | A unique identifier assigned to each Google Sheets spreadsheet. |
Sheet Name | This parameter indicates the name of the sheet within the spreadsheet where the data update will occur. |
From Column | This parameter specifies the starting column from which the data update will begin. It defines the left boundary of the range within the specified sheet where the new data will be inserted or overwritten. |
To Column | This parameter defines the ending column up to which the data update will extend. It marks the right boundary of the range within the specified sheet where the new data will be inserted or overwritten. |
Data From Column Maker | This parameter represents the data to be written into the specified range. It could be dynamically generated data, fetched from another source, or provided by the user. The format and structure of this data should align with the specified range and the sheet's layout.
This is an array of arrays, the outer array representing all the data and each inner array representing a major dimension. Each item in the inner array corresponds with one cell.
Supported value types are: bool, string, and double. Null values will be skipped. To set a cell to an empty value, set the string value to an empty string. (ListValue format: https://protobuf.dev/reference/protobuf/google.protobuf/#google.protobuf.ListValue) |
Write a Row to Sheet (Action Type Call)
This request is aimed at updating the values within a specified range in a Google Sheets spreadsheet. The values are arranged column-wise, each inner array in the values array corresponds to a column in the spreadsheet.
Fields:
Title | Description |
/SpreadsheetID | A unique identifier assigned to each Google Sheets spreadsheet. |
Sheet Name | This parameter indicates the name of the sheet within the spreadsheet where the data update will occur. |
Data From Row Maker | This parameter represents the data to be written into the specified range. It could be dynamically generated data, fetched from another source, or provided by the user. The format and structure of this data should align with the specified range and the sheet's layout.
This is an array of arrays, the outer array representing all the data and each inner array representing a major dimension. Each item in the inner array corresponds with one cell.
Supported value types are: bool, string, and double. Null values will be skipped. To set a cell to an empty value, set the string value to an empty string. (ListValue format: https://protobuf.dev/reference/protobuf/google.protobuf/#google.protobuf.ListValue) |
From Column | This parameter specifies the starting column from which the data update will begin. It defines the left boundary of the range within the specified sheet where the new data will be inserted or overwritten. |
To Column | This parameter defines the ending column up to which the data update will extend. It marks the right boundary of the range within the specified sheet where the new data will be inserted or overwritten. |
Find & Replace (Action Type Call)
This request is designed to execute a find and replace operation within a specified range in a Google Sheets spreadsheet.
Fields:
Title | Description |
/SpreadSheet ID | A unique identifier assigned to each Google Sheets spreadsheet. |
Find This | Specifies the text or pattern to be found within the specified range in the spreadsheet. |
Match Case? | Indicates whether the find operation should be case-sensitive. If set to true, the operation will only match text with the exact same case as specified in the "Find This" parameter. |
Match Entire Cell? | Determines whether the find operation should only match entire cell contents or partial matches within cells. If set to true, the operation will only match cells where the entire content matches the "Find This" parameter. |
Include Formulas? | Defines whether the find operation should include cells containing formulas in addition to values. If set to true, the operation will consider both cell values and formulas during the search. |
Replace With | Specifies the text or pattern to replace the found occurrences of the "Find This" parameter within the specified range. |
Start Column | Indicates the index of the starting column for the range in which the find and replace operation will be performed. |
End Column | Specifies the index of the ending column for the range in which the find and replace operation will be performed. |
Start Row | Specifies the index of the starting row for the range in which the find and replace operation will be performed. |
End Row | Specifies the index of the ending row for the range in which the find and replace operation will be performed. |
Sheet ID | Identifies the specific sheet within the spreadsheet where the find and replace operation will take place. It helps to target the operation to a specific sheet when dealing with multiple sheets within the same spreadsheet. |
Move a Column (Action Type Call)
This request is used to move columns within a Google Sheets spreadsheet.
Fields:
Title | Description |
/SpreadSheet ID | A unique identifier assigned to each Google Sheets spreadsheet. |
Sheet ID | This parameter specifies the unique identifier of the Google Sheets spreadsheet containing the columns to be moved. The sheet ID is a numerical value assigned to each sheet within a spreadsheet and can be found in the URL of the spreadsheet. |
Start From Column | This parameter indicates the index of the first column in the range of columns to be moved. It represents the starting point from which the columns will be selected for the move operation. Column indices are numerical values, with the leftmost column typically being index 0. |
End at Column | This parameter represents the index of the last column in the range of columns to be moved. It specifies the endpoint of the selection of columns for the move operation. Similar to the start column index, it is a numerical value indicating the position of the column within the sheet. |
Move to Column Number | This parameter determines the index of the destination column where the selected columns will be moved to within the sheet. It specifies the target position within the sheet where the columns will be relocated. Column indices are numerical values, with the leftmost column typically being index 0. |
Move a Row (Action Type Call)
This request is useful for reorganizing data within a spreadsheet, allowing users to adjust the arrangement of rows to better suit their needs.
Fields:
Title | Description |
/SpreadSheet ID | A unique identifier assigned to each Google Sheets spreadsheet. |
Sheet ID | This parameter specifies the unique identifier of the Google Sheets spreadsheet containing the rows to be moved. The sheet ID is a numerical value assigned to each sheet within a spreadsheet and can be found in the URL of the spreadsheet. |
Start From Column | Specifies the starting index of the range of rows to be moved within the specified sheet. |
End at Column | Specifies the ending index of the range of rows to be moved within the specified sheet. |
Move to Column Number | Indicates the index of the destination row where the selected rows will be moved within the sheet. |
Auto Resize a Row (Action Type Call)
This request is designed to automatically resize the dimensions of columns within a specified sheet. When you specify a range of columns (identified by their start and end indices) and provide the sheet ID, Google Sheets calculates the optimal width for each column based on the content within them. This process ensures that the columns in your spreadsheet are resized dynamically to best fit the data they contain, providing an optimal viewing experience for users without requiring manual adjustment.
Fields:
Title | Description |
/SpreadSheet ID | A unique identifier assigned to each Google Sheets spreadsheet. |
Sheet ID | This parameter specifies the unique identifier of the sheet within the spreadsheet where you want to perform the automatic resizing of column dimensions. Each sheet in a Google Sheets document has a distinct ID. |
Start Index | This parameter indicates the index of the first column in the range of columns you want to automatically resize. Columns are indexed starting from 0, so the start index represents the position of the leftmost column within the range. |
End Index | This parameter represents the index of the last column in the range of columns you want to automatically resize. Similar to the start index, columns are indexed starting from 0, so the end index denotes the position of the rightmost column within the range. |
Auto Resize a Column (Action Type Call)
This request is designed to automatically resize the dimensions of rows within a specified sheet. This process ensures that the rows in your spreadsheet are resized dynamically to best fit the data they contain, providing an optimal viewing experience for users without requiring manual adjustment.
Fields:
Title | Description |
/SpreadSheet ID | A unique identifier assigned to each Google Sheets spreadsheet. |
Sheet ID | This parameter specifies the unique identifier of the sheet within the spreadsheet where you want to perform the automatic resizing of rows dimensions. Each sheet in a Google Sheets document has a distinct ID. |
Start Index | Specifies the index of the first row in the range of rows you want to automatically resize. Rows are indexed starting from 0, so the start index represents the position of the topmost row within the range. |
End Index | Represents the index of the last row in the range of rows you want to automatically resize. Similar to the start index, rows are indexed starting from 0, so the end index denotes the position of the bottommost row within the range. |
Auto Resize a Column (Action Type Call)
This request is designed to automatically resize the dimensions of rows within a specified sheet. This process ensures that the rows in your spreadsheet are resized dynamically to best fit the data they contain, providing an optimal viewing experience for users without requiring manual adjustment.
Fields:
Title | Description |
/SpreadSheet ID | A unique identifier assigned to each Google Sheets spreadsheet. |
Sheet ID | This parameter specifies the unique identifier of the sheet within the spreadsheet where you want to perform the automatic resizing of rows dimensions. Each sheet in a Google Sheets document has a distinct ID. |
Start Index | Specifies the index of the first row in the range of rows you want to automatically resize. Rows are indexed starting from 0, so the start index represents the position of the topmost row within the range. |
End Index | Represents the index of the last row in the range of rows you want to automatically resize. Similar to the start index, rows are indexed starting from 0, so the end index denotes the position of the bottommost row within the range. |
☝
Hint: * Remember when adding a run of actions that include the data making element, to provide some delays.
Write a Row (ROW TOOL) (Action Type Call)
This request updates the a spreadsheet with all the details mentioned when creating its body request - "Rows Output" from Plugin Element. The updates are made rows-wise, each inner array in the values array corresponds to a row in the spreadsheet.
Fields:
Title | Description |
/SpreadSheet ID | A unique identifier assigned to each Google Sheets spreadsheet. |
body | "Rows Output" from Element here. |
Write a Column (COLUMN TOOL) (Action Type Call)
This request updates the a spreadsheet with all the details mentioned when creating its body request - "Columns Output" from Plugin Element. The updates are made columns-wise, each inner array in the values array corresponds to a column in the spreadsheet.
Fields:
Title | Description |
/SpreadSheet ID | A unique identifier assigned to each Google Sheets spreadsheet. |
body | "Column Output" from Element here. |
Allow Spreadsheet Access (everyone) (Action Type Call)
This request modifies the permissions for a specific Google Sheets spreadsheet identified by its ID, granting a specific role to everyone.
Fields:
Title | Description |
/SpreadSheet ID | A unique identifier assigned to each Google Sheets spreadsheet. |
role | Select a role transferring access rights: writer, reader or owner. |
- Writer: Users with the writer role can view, edit, and comment on the spreadsheet. They can make changes to the content, formatting, and structure of the document.
- Reader: Users with the reader role can only view the contents of the spreadsheet. They cannot make any edits or changes to the document. This role is ideal for sharing information with individuals who only need to view the data.
- Owner: The owner role has full control over the spreadsheet. Owners can view, edit, comment, share, and delete the document. They also have the ability to manage permissions and settings for the spreadsheet.
Allow Spreadsheet Access (user) (Action Type Call)
This request is used to grant a specific role to a user for a spreadsheet.
Fields:
Title | Description |
/SpreadSheet ID | A unique identifier assigned to each Google Sheets spreadsheet. |
role | Specifies the access level granted to the user. This can be "reader", "writer", "owner", or another predefined role. |
emailAddress | The email address of the user to whom the access rights will be granted. |
sendNotificationEmail | This parameter determines whether to send a notification email to the user informing them of the access change. If set to true, the user will receive an email notification; if set to false, no notification will be sent. |
- Writer: Users with the writer role can view, edit, and comment on the spreadsheet. They can make changes to the content, formatting, and structure of the document.
- Reader: Users with the reader role can only view the contents of the spreadsheet. They cannot make any edits or changes to the document. This role is ideal for sharing information with individuals who only need to view the data.
- Owner: The owner role has full control over the spreadsheet. Owners can view, edit, comment, share, and delete the document. They also have the ability to manage permissions and settings for the spreadsheet.
Restrict Spreadsheet Access (Action Type Call)
Deletes the permissions for anyone with the link to access the specified Google Sheets file identified by the Spreadsheet ID.
Once the permissions for anyone with the link are deleted, no one will be able to access the file using the link. Access will be restricted to only those users who have been explicitly granted permission by other means, such as specific email invitations or shared folder access.
Fields:
Title | Description |
/SpreadSheet ID | A unique identifier assigned to each Google Sheets spreadsheet. |
Send Writer rights (Action Type Call)
Request updates permissions for a specific user on a Google Drive spreadsheet. By specifying the user's email address and setting their role to "writer," this request grants the user editing privileges on the spreadsheet. The user's role will transition to an owner once they accept the invitation.
Fields:
Title | Description |
/SpreadSheet ID | A unique identifier assigned to each Google Sheets spreadsheet. |
emailMessage | A text of the message to notify the user to whom you have granted specific rights. |
sendNotificationEmail | If this parameter is true, then user will receive a email notification about these rights. |
User Email | Specify user email address which you want to send these rights. |
Get Files (Drive) (Data Type Call)
Retrieves a list of files from the Google Drive API for the authenticated user. It requires authorization and allows users to access information about files stored in their Google Drive accounts, including file metadata such as title, file size, modification date, and permissions. The response typically includes a list of file objects containing details like file ID, title, MIME type, and other relevant information.
Fields:
Title | Description |
hash | Unique number to make a unique request. Is used for the retrieved data always to be up to date, for it to work right the field value should always be different, best practice is to use the Current date Unix. |
Delete File (Drive) (Action Type Call)
Delete a specific file from Google Drive. Upon successful execution, the file will be permanently removed from the user's Google Drive account.
Fields:
Title | Description |
fileid | The ID of the file. You can get the ID file from the URL if you open the file in the browser or share the link in Google Drive. Example: "https://docs.google.com/spreadsheets/d/ThisIsFileID/edit#gid=123456789" |
Copy File (Drive) (Action Type Call)
This request is used to create a copy of a specific file in Google Drive. Upon successful execution, a new copy of the file will be created in the user's Google Drive account.
Fields:
Title | Description |
fileid | The ID of the file. You can get the ID file from the URL if you open the file in the browser or share the link in Google Drive. Example: "https://docs.google.com/spreadsheets/d/ThisIsFileID/edit#gid=123456789" |
Copy to Another (Action Type Call)
Allows you to copy a specific sheet within a Google Sheets spreadsheet to another location within the same or a different spreadsheet. Upon successful execution, a copy of the specified sheet will be created in the target location.
Fields:
Title | Description |
/SpreadsheetID | Refers to the unique identifier of the source spreadsheet from which you want to copy the sheet. |
/SheetID | Represents the specific identifier of the sheet within the source spreadsheet that you want to copy. |
Destination Spreadsheet ID | The unique identifier of the destination spreadsheet where the copied sheet will be created. |
Changelogs
Update 29.07.2022 - Version: 1.15.0
- Fixed data call to read chart data from the sheet
Update 01.12.2022 - Version: 1.16.0
- Changed API Call "Transfer Ownership" to "Send Writer rights".
Update 07.12.2022 - Version: 1.17.0
- Added show docs for a "Send Writer rights" action.
Update 28.03.2023 - Version: 1.20.0
- Updated documentation for actions.