Google Sheets
Google Sheets is a spreadsheet program included as part of a free, web-based software office suite offered by Google within its Google Drive service.
- List of Pre-defined Google Sheets Functions:
Using the Google Sheets node, the user can automate the following functions.- Create Worksheet: Use this expression to create a Google sheet by sheet id or sheet name.
Input Parameters:
Input Parameter Name Description URL URL of the spreadsheet Sheet_name Name of the worksheet to be created Row_count Number of rows to be created Col_count Number of columns to be created Creds_path File path where credentials.json file is placed. This file will be downloaded to the client machine during G-suite setup process. For more details, refer Section 2. Setting up G-suite.
Output Parameters:
Output Parameter Name Description Message Returns the message (“Sheet is created successfully”)
- Delete worksheet: Use this expression to delete the worksheet in
Google Sheets.
Input Parameters:
Input Parameter Name Description URL URL of the spreadsheet Sheet_name_or_index Sheet name or index of the worksheet
Creds_path File path where credentials.json file is placed. This file will be downloaded to the client machine during G-suite setup process. For more details, refer Section 2. Setting up G-suite.
Output Parameters:
Output Parameter Name Description Message Returns the message (“Sheet is deleted successfully”)
- Get all values: Use this expression to get all the values in Google sheet.
Input Parameters:
Input Parameter Name Description URL URL of the spreadsheet Sheet_name_or_index Sheet name or index of the worksheet
Creds_path File path where credentials.json file is placed. This file will be downloaded to the client machine during G-suite setup process. For more details, refer Section 2. Setting up G-suite.
Output Parameters:
Output Parameter Name Description Cell values Returns a list all values in the sheet
- Get row values: Use this expression to get the values of a
specified row in Google sheet.
Input Parameters:
Input Parameter Name Description URL URL of the spreadsheet Sheet_name_or_index Sheet name or index of the worksheet Row_index Row number from which values to be retrieved
Creds_path File path where credentials.json file is placed. This file will be downloaded to the client machine during G-suite setup process. For more details, refer Section 2. Setting up G-suite.
Output Parameters:
Output Parameter Name Description Row values Returns a list of all values in the row index specified.
- Get row count: Use this expression to get the count of the row in Google sheet.
Input Parameters:
Input Parameter Name Description URL URL of the spreadsheet Sheet_name_or_index Sheet name or index of the worksheet
Creds_path File path where credentials.json file is placed. This file will be downloaded to the client machine during G-suite setup process. For more details, refer Section 2. Setting up G-suite.
Output Parameters:
Output Parameter Name Description Row count Returns the count of the rows which has data.
- Get Cell Value: Use this expression to get the cell value in the Google sheet.
Input Parameters:
Input Parameter Name Description URL URL of the spreadsheet Sheet_name_or_index Sheet name or index of the worksheet Row_index Row number from which values to be retrieved Col_index Column index of the cell
Creds_path File path where credentials.json file is placed. This file will be downloaded to the client machine during G-suite setup process. For more details, refer Section 2. Setting up G-suite.
Output Parameters:
Output Parameter Name Description Cell value Returns the cell value.
- Update cell: Use this expression to update the cell in Google sheet.
Input Parameters:
Input Parameter Name Description URL URL of the spreadsheet Sheet_name_or_index Sheet name or index of the worksheet Row_index Row number from which values to be retrieved Col_index Column index of the cell Value Pass the value to be inserted
Creds_path File path where credentials.json file is placed. This file will be downloaded to the client machine during G-suite setup process. For more details, refer Section 2. Setting up G-suite.
Output Parameters:
Output Parameter Name Description Message Returns the message (“cell value is updated”)
- Find all cells: Use this expression to find the total number of cells in a Google sheet.
Input Parameters:
Input Parameter Name Description URL URL of the spreadsheet Sheet_name_or_index Sheet name or index of the worksheet Value_to_be_searched Pass the value to be searched
Creds_path File path where credentials.json file is placed. This file will be downloaded to the client machine during G-suite setup process. For more details, refer Section 2. Setting up G-suite.
Output Parameters:
Output Parameter Name Description Output Returns the count of number of cells.
- Find cell: Use this expression to find the cell in Google sheet.
Input Parameters:
Input Parameter Name Description URL URL of the spreadsheet Sheet_name_or_index Sheet name or index of the worksheet Value_to_be_searched Pass the value to be searched
Creds_path File path where credentials.json file is placed. This file will be downloaded to the client machine during G-suite setup process. For more details, refer Section 2. Setting up G-suite.
Output Parameters:
Output Parameter Name Description Output Returns the row and column index of the cell.
- Get column values: Use this expression to get the column values in a Google sheet.
Input Parameters:
Input Parameter Name Description URL URL of the spreadsheet Sheet_name_or_index Sheet name or index of the worksheet Col_index Column number from which values to be retrieved
Creds_path File path where credentials.json file is placed. This file will be downloaded to the client machine during G-suite setup process. For more details, refer Section 2. Setting up G-suite.
Output Parameters:
Output Parameter Name Description Column values Returns a list of all values in the column index specified.
- Delete row by index: Use this expression to delete a row in the Google sheet.
Input Parameters:
Input Parameter Name Description URL URL of the spreadsheet Sheet_name_or_index Sheet name or index of the worksheet Row_index Pass the row to be deleted
Creds_path File path where credentials.json file is placed. This file will be downloaded to the client machine during G-suite setup process. For more details, refer Section 2. Setting up G-suite.
Output Parameters:
Output Parameter Name Description Message Returns the message (“row is deleted successfully”).
- Delete row by index: Use this expression to delete a row in the Google sheet.
Input Parameters:
Input Parameter Name Description URL URL of the spreadsheet Sheet_name_or_index Sheet name or index of the worksheet New_sheetname New sheet name
Creds_path File path where credentials.json file is placed. This file will be downloaded to the client machine during G-suite setup process. For more details, refer Section 2. Setting up G-suite.
Output Parameters:
Output Parameter Name Description Message Returns the message (“Sheet has been renamed successfully”).
- Import csv data: Use this expression to import csv file in
Google sheet.
Input Parameters:
Input Parameter Name Description URL URL of the spreadsheet
Creds_path File path where credentials.json file is placed. This file will be downloaded to the client machine during G-suite setup process. For more details, refer Section 2. Setting up G-suite. Csv_path Csv File path
Output Parameters:
Output Parameter Name Description Message Returns the message (“csv data imported to the first sheet”).
- Append column: Use this expression to append column in a Google sheet.
Input Parameters:
Input Parameter Name Description URL URL of the spreadsheet Sheet_name_or_index Sheet name or index of the worksheet No_of_columns Number of columns to be appended
Creds_path File path where credentials.json file is placed. This file will be downloaded to the client machine during G-suite setup process. For more details, refer Section 2. Setting up G-suite.
Output Parameters:
Output Parameter Name Description Message Returns the message (“column(s) appended successfully”).
- Add row: Use this expression to append row in a Google sheet.
Input Parameters:
Input Parameter Name Description URL URL of the spreadsheet Sheet_name_or_index Sheet name or index of the worksheet No_of_rows Number of rows to be appended
Creds_path File path where credentials.json file is placed. This file will be downloaded to the client machine during G-suite setup process. For more details, refer Section 2. Setting up G-suite.
Output Parameters:
Output Parameter Name Description Message Returns the message (“row(s) appended successfully”).
- Copy Range: Use this expression to copy a range of cells in a Google sheet.
Input Parameters:
Input Parameter Name Description URL URL of the spreadsheet Sheet_name_or_index Sheet name or index of the worksheet Rng Range of cells that must be copied
Creds_path File path where credentials.json file is placed. This file will be downloaded to the client machine during G-suite setup process. For more details, refer Section 2. Setting up G-suite.
Output Parameters:
Output Parameter Name Description Message Returns the message (“cell(s) copied successfully”).
- Delete column: Use this expression to delete column in a Google sheet.
Input Parameters:
Input Parameter Name Description URL URL of the spreadsheet Sheet_name_or_index Sheet name or index of the worksheet Start_index Pass the start index of the column End_index Pass the end index of the column
Creds_path File path where credentials.json file is placed. This file will be downloaded to the client machine during G-suite setup process. For more details, refer Section 2. Setting up G-suite.
Output Parameters:
Output Parameter Name Description Message Returns the message (“columns deleted successfully”).
- Update cells: Use this expression to update the range of cells in a Google sheet.
Input Parameters:
Input Parameter Name Description URL URL of the spreadsheet Sheet_name_or_index Sheet name or index of the worksheet Row_index Row index of the cell Col_index Column index of the cell Value Pass the value to be inserted
Creds_path File path where credentials.json file is placed. This file will be downloaded to the client machine during G-suite setup process. For more details, refer Section 2. Setting up G-suite.
Output Parameters:
Output Parameter Name Description Message Returns the message (“cell value is updated”).
- Delete range: Use this expression to delete the range of cells in a Google sheet.
Input Parameters:
Input Parameter Name Description URL URL of the spreadsheet Sheet_name_or_index Sheet name or index of the worksheet Rng Range of the sheet to be deleted ShiftDimension Direction of cell shift Value Pass the value to be inserted
Creds_path File path where credentials.json file is placed. This file will be downloaded to the client machine during G-suite setup process. For more details, refer Section 2. Setting up G-suite.
Output Parameters:
Output Parameter Name Description Message Returns the message (“range of values is deleted successfully”).
- Get worksheets: Use this expression to get sheets name.
Input Parameters:
Input Parameter Name Description URL URL of the spreadsheet
Creds_path File path where credentials.json file is placed. This file will be downloaded to the client machine during G-suite setup process. For more details, refer Section 2. Setting up G-suite.
Output Parameters:
Output Parameter Name Description Message Returns a list of all the sheet names.
- Designing a Task Using a Google Sheets Node:
Do the following to use a Google Sheets node in a task:- Use an existing task or create a new task based on the requirement.
Note: Refer Create A Task for more details on how to create a task and how to open an existing task. - Drag and drop the Google Sheets node from the Objects -> G-Suite section and connect the nodes.
- Click on the Google Sheets node and specify the properties.
- Double-click on the Google Sheets node to display the Edit UI dialog.
- The following options are available in the Edit UI dialog:
- Google Sheets: When the user selects the Google Sheets option, pre-defined Google Sheets functions are available in the drop-down following it.
- Set Variable: The user can set a variable inside the JIRA node. The value to be set can either be a constant or another variable. If this variable is set as an output variable in the Properties tab, it can be made available for other nodes as well.
- Default Expression: These are Jiffy provided python expressions for the commonly used functions.
Example: Concat, Capitalize, Add days etc. - Custom Expression: Jiffy provides a feature to allow users to create custom Python expressions and use them in the Edit UI dialog. The Custom Expressions need to be uploaded to Jiffy Repository (Repository ->Custom Expressions). Once it is uploaded, it is available in the drop down of Custom Expressions option. For details on how to create custom expressions, click here.
- Google Sheets: When the user selects the Google Sheets option, pre-defined Google Sheets functions are available in the drop-down following it.
- Select the Google Sheets option and then select the required function, for example: Create Sheet.
- Click on the highlighted icon and then specify the below details:
Input Parameters:
Input Parameter Name Description URL URL of the spreadsheet Sheet_name Name of the worksheet to be created Row_count Number of rows to be created Col_count Number of columns to be created Creds_path File path where credentials.json file is placed. This file will be downloaded to the client machine during G-suite setup process. For more details, refer Section 2. Setting up G-suite.
- Select the output variable that is created while specifying properties.
- Similarly, select the required functions in the *Edit UI** dialog and then click on the Save icon.
- Click on the Save button in the Task Design screen.
- Click on the Trail Run button.
- Select the Google Sheets node and click on the book icon to
display the Result of execution window.
- Based on the provided details, a Google sheet will be created.
- Use an existing task or create a new task based on the requirement.
- Create Worksheet: Use this expression to create a Google sheet by sheet id or sheet name.