Google Sheets & Charts Service Plugin

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

You must have a Google Service Account, if you do not then just follow our steps.
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.
Image without caption
  • Create a new service account: Click on the "Create Service Account" button.
Image without caption
  • 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.
Image without caption
Image without caption
  • 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.
Image without caption
  • 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.
Image without caption
Image without caption
b) ”private key” and fill the “Private key” field in the “plugins” tab of your app.
Image without caption
Image without caption
  • 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
Image without caption
  • 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

Sheets Value Parser

Plugin API Calls

Create Spreadsheet (Action Type Call)

💡
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=0

Get Spreadsheet (Action Type Call)

Get Spreadsheet (Data Type Call)

Get Spreadsheet Properties (Data Type Call)

Get Spreadsheet Properties (Action Type Call)

Get Sheet Values (Range) (Data Type Call)

Get Sheet Values (Column) (Data Type Call)

Get Sheet Values (Formula) (Data Type Call)

Get Sheet Values (Formatted Value) (Data Type Call)

Get Sheet Values (Row) (Data Type Call)

Add Sheet to Spreadsheet (Action Type Call)

Remove Sheet from Spreadsheet (Action Type Call)

Clear Ranges (Action Type Call)

Create Spreadsheet (Custom) (Action Type Call)

Update a Spreadsheet (Custom) (Action Type Call)

Add Charts to Spreadsheet (Custom) (Action Type Call)

Delete Chart From Spreadsheet (Custom) (Action Type Call)

Add Protected Range (Action Type Call)

Conditional Formatting Spreadsheet (Custom) (Action Type Call)

Data Validation Spreadsheet (Action Type Call)

Move/Resize Chart From Spreadsheet (Custom) (Action Type Call)

Read Charts on Spreadsheet (Custom) (Data Type Call)

Style Sheet Header (Action Type Call)

Copy Sheet to Another (Action Type Call)

Add Empty Rows to Sheet (Action Type Call)

Add Empty Columns to Sheet (Action Type Call)

Write to a Cell (Action Type Call)

Write a Column Sheet (Action Type Call)

Write a Row to Sheet (Action Type Call)

Find & Replace (Action Type Call)

Move a Column (Action Type Call)

Move a Row (Action Type Call)

Auto Resize a Row (Action Type Call)

Auto Resize a Column (Action Type Call)

Auto Resize a Column (Action Type Call)

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)

Write a Column (COLUMN TOOL) (Action Type Call)

Allow Spreadsheet Access (everyone) (Action Type Call)

Allow Spreadsheet Access (user) (Action Type Call)

Restrict Spreadsheet Access (Action Type Call)

Send Writer rights (Action Type Call)

Get Files (Drive) (Data Type Call)

Delete File (Drive) (Action Type Call)

Copy File (Drive) (Action Type Call)

Copy to Another (Action Type Call)

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.

Powered by Notaku
Share
Content