Google Sheets

Converts a Google sheet into a JSON document or converts a JSON document into a Google sheet.

Revision History

1.0.0.1 - Initial Release.

Properties

Connection

ConnectionData
Type: Json
Key downloaded from google

Action

Type: List Input
Open an existing document, create a new document or delete a document. Read, update and delete sheet data.

Read – Read data from a sheet in a Google document.
Create – Create a sheet on a new document or add it to an existing document.
Replace – Replace data on an existing sheet.
Append– Append data on an existing sheet.
DeleteSheet– Delete a sheet.
DeleteFile– Delete a document.

FolderId

Type: String Input
Folder ID for the GoogleSheet document.

The id can be found in the link of the folder on Google Drive desktop or the end of the URL on your internet browser.

FileName

Type: String Input
The name of the file to create, read update or delete.

SheetName

Type: String Input
The name of the Sheet to create, read update or delete.

HasColumnNames

Type: Boolean Input
When true, indicates that the first row of the flat file contains column names.

Document

Type: Multiline Text InputOutput
The Json document which is returned when reading a GooleSheet document, used when creating a document or inserting it into an existing document.

Remarks

Creating a Connection

Note: This node uses a droppoint, To run it from cloud, please contact support to add the required assemblies to your site.

Follow these steps to obtain a Google service account key:

Create a project and service account

Next to the Google Cloud Logo, select the dropdown.
Select a project or create a new project.
Click on Credentials on the left navigation menu.
Click “Create Credentials”
Select “Service account”
Add a service account name and click “create and continue”
Roles can be left on the default option.
Click Done.

Create a new key

Select IAM & Admin on the left navigation menu.
Then click service accounts.
Select your account.
On the details tab copy the email address (This will be used in Google Drive).
Select the Keys tab and select “ADD KEY”.
From the dropdown list select “Create new key” (Key type JSON).
A JSON document will automatically download.

Enable APIs

Select APIs & Services on the left navigation menu
Click on enable APIs and services
Search for Google Drive API and Google Sheets API
Click on it and enable these APIs

Google Drive

Log in to your Google Drive
Right-click the folder to use in Flowgear
Select share
Add the service account email and share as editor

Flowgear Google Sheets Connection

Add the contents of the downloaded JSON file to the connection property - ConnectionData.

FolderId, FileName, SheetName are mandatory fields to create a document or update a document.

Create action, if the FileName exists, a new sheet will be created.

Google Drive allows for duplicate file names in a single folder, This will return an error when searching for the file. (More than one file matched the specified name in the specified folder.)

When deleting a sheet, if there is only one sheet in the file, the file will be deleted.

Troubleshooting

Unable to run Workflow. GoogleSheets (GoogleSheets 1.0.0.7): Failed to read data.

When utilizing the Google Sheets Node, ensure that there are no Duplicate Column Names, as this could cause the node to fail, resulting in the above error.

You can address this issue by either renaming the duplicated column name or removing it from your Sheet.