Module trigger.google.sheets
![](https://bcentral-packageicons.azureedge.net/images/ballerinax_trigger.google.sheets_0.6.0.png)
ballerinax/trigger.google.sheets Ballerina library
Overview
The Ballerina listener for Google Sheets provides the capability to listen to simple events using the App Scripts triggers. App Scripts runs a function automatically whenever a certain event occurs, for example, when a user changes a value in a spreadsheet. When an event is triggered, Apps Script passes the event object ( typically e
) as an argument to the function. The event object contains information about the context that caused the event to trigger. Using App Script Installable triggers we can invoke services that require authorization and pass the event information. The Google Sheets Ballerina listener can listen to these events triggered and execute the user logic based on the event type received.
The trigger can listen to events triggered when a spreadsheet is edited such as when a row is appended to a spreadsheet or when a row is updated in a spreadsheet with the following trigger methods:
onAppendRow
onUpdateRow
This module supports Google App Scripts.
Prerequisites
Before using this connector in your Ballerina application, complete the following:
-
Create Google account
-
Enable Google App Script trigger to listen to internal changes of a spreadsheet.
-
Open the google sheet.
-
Navigate to
Extensions > Apps Script
. -
Name your project. (Example: Name the project
GSheet_Ballerina_Trigger
) -
Remove all the code that is currently in the Code.gs file, and replace it with this:
function atChange(e){ if (e.changeType == "REMOVE_ROW") { saveDeleteStatus(1); } } function atEdit(e){ var source = e.source; var range = e.range; var a = range.getRow(); var b = range.getSheet().getLastRow(); var previousLastRow = Number(getValue()); var deleteStatus = Number(getDeleteStatus()); var eventType = "edit"; if ((a == b && b != previousLastRow) || (a == b && b == previousLastRow && deleteStatus == 1)) { eventType = "appendRow"; } else if ((a != b) || (a == b && b == previousLastRow && deleteStatus == 0)) { eventType = "updateRow"; } var formData = { 'spreadsheetId' : source.getId(), 'spreadsheetName' : source.getName(), 'worksheetId' : range.getSheet().getSheetId(), 'worksheetName' : range.getSheet().getName(), 'rangeUpdated' : range.getA1Notation(), 'startingRowPosition' : range.getRow(), 'startingColumnPosition' : range.getColumn(), 'endRowPosition' : range.getLastRow(), 'endColumnPosition' : range.getLastColumn(), 'newValues' : range.getValues(), 'lastRowWithContent' : range.getSheet().getLastRow(), 'lastColumnWithContent' : range.getSheet().getLastColumn(), 'previousLastRow' : previousLastRow, 'eventType' : eventType, 'eventData' : e }; var payload = JSON.stringify(formData); var options = { 'method' : 'post', 'contentType': 'application/json', 'payload' : payload }; UrlFetchApp.fetch('<BASE_URL>/', options); saveValue(range.getSheet().getLastRow()); saveDeleteStatus(0); } var properties = PropertiesService.getScriptProperties(); function saveValue(lastRow) { properties.setProperty('PREVIOUS_LAST_ROW', lastRow); } function getValue() { return properties.getProperty('PREVIOUS_LAST_ROW'); } function saveDeleteStatus(deleteStatus) { properties.setProperty('DELETE_STATUS', deleteStatus); } function getDeleteStatus() { return properties.getProperty('DELETE_STATUS'); }
We’re using the UrlFetchApp class to communicate with other applications on the internet.
-
Replace the <BASE_URL> section with the base URL where your listener service is running. (Note: You can use ngrok to expose your web server to the internet. Example: 'https://7745640c2478.ngrok.io/')
-
Navigate to the
Triggers
section in the left menu of the editor. -
Click
Add Trigger
button. -
Select the following values for the following fields.
Field Value Choose which function to run atChange
Select event source From spreadsheet
Select event type On change
-
This will prompt you to authorize your script to connect to an external service. Click
Review Permissions
and thenAllow
to continue. -
Repeat the same process, add a new trigger with the following values for the following fields. Then click Save!.
Field Value Choose which function to run atEdit
Select event source From spreadsheet
Select event type On edit
-
You have now configured your triggers. You can test these triggers by editing the Google sheet.
-
Quickstart
To use the Google Sheets listener in your Ballerina application, update the .bal file as follows:
Step 1: Import listener
Import the ballerinax/trigger.google.sheets module into the Ballerina project.
import ballerinax/trigger.google.sheets;
Step 2: Create a new listener instance
Create a sheets:ListenerConfig
with the spreadsheet ID obtained, and initialize the listener with it.
configurable sheets:ListenerConfig userInput = { spreadsheetId: "1rqmQttRXGYSYJheibCpVCYXBa4jmggrEXpcgH2ahk94" }; listener sheets:Listener sheetListener = new (userInput);
!!! NOTE: Spreadsheet ID is available in the spreadsheet URL "https://docs.google.com/spreadsheets/d/" + <SPREADSHEET_ID> + "/edit#gid=" + <WORKSHEET_ID>
Step 3: Invoke listener triggers
-
Now you can use the triggers available within the listener.
Following is an example on how to listen to append events and update events of a spreadsheet using the listener. Add the trigger implementation logic under each section based on the event type you want to listen to using the Google sheets Listener.
Listen to append events and update events
service sheets:SheetRowService on sheetListener { remote function onAppendRow(sheets:GSheetEvent payload) returns error? { json? eventData = payload?.eventData; // Write your logic here..... } remote function onUpdateRow(sheets:GSheetEvent payload) returns error? { json? eventData = payload?.eventData; // Write your logic here..... } }
!!! NOTE: The Google Sheets Trigger can listen to events triggered when a spreadsheet is edited such as when a new row is appended or when a row is updated with the following trigger methods:
onAppendRow
,onUpdateRow
. We can get more information about the edit event such as thespreadsheet ID, spreadsheet name, worksheet ID, worksheet name, range updated, starting row position, end row position, starting column position, end column position, new values, last row with content, last column with content
etc. -
Use
bal run
command to compile and run the Ballerina program.