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.

  1. Open the google sheet.

  2. Navigate to Extensions > Apps Script.

  3. Name your project. (Example: Name the project GSheet_Ballerina_Trigger)

  4. 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.

  5. 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/')

  6. Navigate to the Triggers section in the left menu of the editor.

  7. Click Add Trigger button.

  8. 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

  9. This will prompt you to authorize your script to connect to an external service. Click Review Permissions and then Allow to continue.

  10. 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

  11. 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

1. 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 the spreadsheet 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.

2. Use bal run command to compile and run the Ballerina program.