Google Spreadsheets

Google Spreadsheets



Google Spreadsheets

Google Spreadsheets Plugin has been designed to perform tasks on spreadsheets on Google Drive.

Prerequisites:

  1. Google developer console account should be created and subscribed   - https://console.developers.google.com/.
  2. APIs for Google Drive and Google Spreadsheet should be enabled.
  3. Client ID and Client Secret should be generated from the Credentials tab. In exchange for the Client ID and Client Secret, a Refresh Token can be generated.

Note: 

  1. Google Spreadsheets plugin steps read from Google Drive. Google Drive differentiates between Excel files and Google Spreadsheet files. However, Google Spreadsheets plugin steps support only Google Spreadsheet files.


References:

Google Spreadsheet documentation

https://developers.google.com/sheets/api


Refer Appendix 15: Google Refresh Token Generation for enabling APIs, creating Client ID and Client Secret and generating Refresh Token.




Append Row

Description

Google Spreadsheet: Append Spreadsheet Plugin Step appends a spreadsheet on Google Drive.


Configurations

No.

Field Name

Description

1

Step name

Name of the step as it appears in the workflow workspace. This name has to be unique in a single workflow.


Connection:

Refer Prerequisites and References above to get the connection attributes.

2

Client ID

Specify Client ID of the Google application.

The data type is String. This field is mandatory.


Client Secret:


3

Accept Values from fields

Leave checkbox unchecked to accept Client Secret value from a field in the previous steps of the stream using a drop down list. 

Else enable checkbox for Secret Key field to appear as Text box.

4

Client Secret

Specify Client Secret of the Google application.


Client Secret is entered using a widget. If checkbox above is enabled Client Secret field appears as Text box and accepts static values or variables. Else if checkbox above is disabled Client Secret field appears as a drop down containing values from previous steps - to select from.

The data type is String. This field is mandatory.


Refresh Token:


5

Accept Values from fields

Leave checkbox unchecked to accept Secret Key value from a field in the previous steps of the stream using a drop down list. 

Else enable checkbox for Secret Key field to appear as Text box.

6

Refresh Token

Specify a Refresh Token to obtain a new Access Token.

Note: 

Refresh Token expires if idle for 6 months.


Refresh Token is entered using a widget. If checkbox above is enabled Refresh Token field appears as Text box and accepts static values or variables. Else if checkbox above is disabled Refresh Token field appears as a drop down containing values from previous steps - to select from.

The data type is String. This field is mandatory.


Input Tab:

No.

Field Name

Description


Input Fields:


1

Spreadsheet ID

Specify the unique ID allocated to the spreadsheet. This ID is the value between the "/d/" and the "/edit" in the URL of your spreadsheet.


(e.g. 

Spreadsheet ID reference URL - For example, consider the following URL that references a Google Sheets spreadsheet: 

https://docs.google.com/spreadsheets/d/1CJnkal5pj3SLpv-HdmyguX/edit#gid=0

Then, Spreadsheet ID is: 1CJnkal5pj3SLpv- HdmyguX) as highlighted above.

2

Sheet Name

The unique name of the sheet such as Sheet1, Sheet2 likewise.


Fields Tab:

No.

Field Name

Description


Button: Get Fields 

Click this button to fetch the field values from previous steps.

1

Table Fields:



Name 

Specify the field Names (or Columns) for the rows you wish to append. Names are populated by clicking the Get Fields button above from previous steps in the workflow stream. 

You may keep all field Names populated or remove some field names for the rows you wish to append to this sheet. 


There are other scenarios while working with this step. You may use the Get Fields button above to fetch field Names. You may click on this button again for any reasons such as new fields in the workflow stream etc. In such a case if data is already present in the step a pop-up windows is displayed with four options as seen in the snapshot below. 



You may choose to add only new rows, Add all rows again, Clear existing rows and add all rows or cancel Get Fields button.

2

Type 

The data Type of field Names above. Type values are only for user information purposes on plugin window. 


Note:

At least one combination of Name and Type should be provided.


Common Buttons:

No.

Field Name

Description


Buttons:


1

OK

On click of this button. It will check the field values.  If any required field values are missing then it will display validation error message. 

If all the required field values are provided then it will save the field values.

2

Cancel

On click of this button, it will cancel the window and do not save any values




Read

Description

Google Spreadsheets: Read step reads a spreadsheet on Google Drive.


Configurations

No.

Field Name

Description

1

Step name

Name of the step as it appears in the workflow workspace. This name has to be unique in a single workflow.


Connection:

Refer Prerequisites and References above to get the connection attributes.

2

Client ID

Specify Client ID of the Google application.

The data type is String. This field is mandatory.


Client Secret:


3

Accept Values from fields

Leave checkbox unchecked to accept Client Secret value from a field in the previous steps of the stream using a drop down list. 

Else enable checkbox for Secret Key field to appear as Text box.

4

Client Secret

Specify Client Secret of the Google application.


Client Secret is entered using a widget. If checkbox above is enabled Client Secret field appears as Text box and accepts static values or variables. Else if checkbox above is disabled Client Secret field appears as a drop down containing values from previous steps - to select from.

The data type is String. This field is mandatory.


Refresh Token:


5

Accept Values from fields

Leave checkbox unchecked to accept Secret Key value from a field in the previous steps of the stream using a drop down list. 

Else enable checkbox for Secret Key field to appear as Text box.

6

Refresh Token

Specify a Refresh Token to obtain a new Access Token.

Note: 

Refresh Token expires if idle for 6 months.


Refresh Token is entered using a widget. If checkbox above is enabled Refresh Token field appears as Text box and accepts static values or variables. Else if checkbox above is disabled Refresh Token field appears as a drop down containing values from previous steps - to select from.

The data type is String. This field is mandatory.


Input Tab:

No.

Field Name

Description


Input Fields:


1

Spreadsheet ID

Specify the unique ID allocated to the spreadsheet. This ID is the value between the "/d/" and the "/edit" in the URL of your spreadsheet.


(e.g. 

Spreadsheet ID reference URL - For example, consider the following URL that references a Google Sheets spreadsheet: 

https://docs.google.com/spreadsheets/d/1CJnkal5pj3SLpv-HdmyguX/edit#gid=0

Then, Spreadsheet ID is: 1CJnkal5pj3SLpv- HdmyguX) as highlighted above.

2

Range

Range should be provided in A1 notation.


This is a string like Sheet1!A1:B2, that refers to a group of cells in the spreadsheet, and is typically used in formulas.

3

Has Header?

Allowed values are ‘Yes’ or ‘No’. Set “Yes” if header row is present in the retrieved data.


If this option is set to ‘Yes’, then values present in all columns in the first row will be populated in Field Name in the Output tab below; else dummy column names such as Field_000, Field_001 and so on will be populated.


Output Tab:

No.

Field Name

Description

1

Button: Get Output Fields 

Click this button to populate header field names (or columns) to be read from spreadsheet in the tabular Field Names below. 


Note: On an ongoing basis please click Get Output Fields button to after any change in any of the above input fields.


Table Fields:


2

Field Name 

Specify the Field Names (or columns) to fetch from the range specified in the input Tab. The Field Names are fetched by clicking the Get Output Fields button above. You may Keep all the Field Names retrieved or deleted the fields names not required.


If ‘Has Header’ is set to Yes in the Input Tab, then values present in all columns in the first row are populated in the Field name. 

If ‘Has Header’ is set to No, then the dummy column names such as Field_000, Field_001 and so on are populated.


The following is true of the rows of data read from the range specified,

  1. If Has Header=Yes  and you wish to fetch the columns of rows data that do not have a value in the first row of header Field Name then alternatively provide an Output Field Name below, to match the columns of rows data. 
  2. In case of merged cells the value is assigned to the top left cell.
  3. In a row, if a cell is empty it is marked as <null>, and also in case there are no consecutive cells having data after that cell then the value of such cells is also marked as <null>.

Note:

At least one combination of Field Name and Output Field Name should be provided.

3

Output Field Name 

You may specify desirable names for the Field Names above; especially when ‘Has Header’=No and Field Names do not represent appropriate business names.


Note:

At least one combination of Field Name and Output Field Name should be provided.


Common Buttons:

No.

Field Name

Description


Buttons:


1

OK

On click of this button. It will check the field values.  If any required field values are missing then it will display validation error message.

If all the required field values are provided then it will save the field values.

2

Cancel

On click of this button, it will cancel the window and do not save any values








      Links to better reach 

            Bot Store

             EPD