Google Sheet

Google Sheets is a cloud-based spreadsheet software developed by Google . It offers a wide range of features for creating, editing, and collaborating on spreadsheets in real-time.

GSheet Integration

Google Sheets offers various ways to integrate with other services and tools to enhance functionality and automate tasks. Here are some common methods for Google Sheets integration:

Follow these Steps:

  1. Open the Google Sheets

  2. Click on Extensions

  3. Open the App Script and paste the Below Code.

  4. Google Apps Script: Google Apps Script is a JavaScript-based scripting language that allows you to automate tasks and extend Google Workspace (formerly G Suite) applications. You can use Google Apps Script to create custom functions, automate data import/export, and even build custom menu options within Google Sheets. To get started, go to "Extensions" -> "Apps Script" within Google Sheets.

  1. Google Sheets API : The Google Sheets API allows you to programmatically access and modify Google Sheets data. This is more advanced and requires coding skills. You can use the API to read and write data, format cells, and perform various operations on your Google Sheets. Documentation and examples can be found on the Google Sheets API documentation.

Copy this Code and Paste in App Script

function onEdit(e) {

  editFun(e)

}

function onChange(e) {

  var sheet = SpreadsheetApp.getActiveSheet();

  var range = sheet.getActiveRange();

  var row = range.getLastRow();

  SpreadsheetApp.getActiveSpreadsheet().toast(">" + e.changeType + " " + row + " " + sheet.getName());

}

function editFun(e) {

  var range = e.range;

  Logger.log("hello this is onEdit");

  const as = e.source.getActiveSheet();

  const name = e.source.getActiveSheet().getName();

  console.log("postDataValue: ", name)

  //Step 1 : Set your Trigger Value 
  var triggerValue = 6

  if (name == "Sheet1" && e.range.columnStart == triggerValue) {

    var row = e.range.rowStart;

    console.log("rangeData: ", e.range.columnStart)

    var headers = getHeadrs(triggerValue)

    console.log("headers: ", headers)

    var data = {}

    for (let i = 0; i < triggerValue; i++) {

      data[headers[0][i].toString()] = as.getRange(row, i + 1).getValue().toString()

    }

    console.log("rangeDataDetails: ", data, `${data["Name"]}`)

    if (Object.keys(data).length > 0) {

      const payload =

      //Step 2 : Paste your Template Json here       
      {

        "to": `${data["Phone"]}`,

        "type": "template",

        "template": {

          "id": "1370745846809427",

          "namespace": "ae3cbb41_4ec3_48af_9a58_68a1c0332ce6",

          "language": {

            "code": "en"

          },

          "name": "ca",

          "components": [{ "type": "header", "parameters": [{ "type": "image", "image": { "link": "https://storage.googleapis.com/wtx_public_bucket_prod/1696485239038.png" } }] }, { "type": "body", "parameters": [{ "type": "text", "text": "Shashi" }] }]

        }
      }

    

    const options = {

          method: 'POST',

          // followRedirects: true,

          // muteHttpExceptions: true,

          payload: JSON.stringify(payload),

          contentType: 'application/json',

          headers: {

            //Step 3 : Paste your API Key here 
            "x-api-key": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJidXNpbmVzc0lkIjoiNjJjODFiZWM3MTVlOGRlYTZlYjQ2MjcyIiwia2V5VmVyc2lvbiI6Ind0Yl85MjMwZmE1NTQxMGUiLCJpYXQiOjE2OTA1Mzg1NzB9.tCIUxL5xZdb1SLU5BjAbNpYuOLgbgjX6Y8dvDTSHPqM",

            "content-type": "application/json"

          }

        };

        try {
          //Step 4: Paste your API number below & remove the bracket
          const res = UrlFetchApp.fetch("https://api.whatstool.business/developers/v1/messages/{Paste your API Number}", options).getContentText()

      console.log("UrlFetchAppResponse: ", JSON.stringify(res))

        } catch(e) {

          console.log("UrlFetchAppError: ", e)

        }

      }

      console.log("postDataValue: ", data)

    }

  }


function getHeadrs(e) {

  return header = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()

    .getRange(1, 1, 1, 6).getValues();

  // console.log("headers: ", header[0].toString())

}

Steps 1 : In this you need to Change the Trigger Value in Which Column the Data Ends take that count and enter it as Trigger value.

Step 2 : Copy the Template JSON from WhatsTool **Go to WhatsTool Business > Settings > Templates > Select the Template > Copy the JSON.

Step 3 :** Change The API Key Go to WhatsTool Business > Settings > Developers API > Generate the API Key and Copy that

Step 4 : Kindly Click on Triggers and Change the status as below Image and Save.

Step 5 : Kindly Add new Entry in the Excel Sheet.

Step 6 : Click on Execution to check the Logs of the message Triggered.

Kindly refer the below video for better understanding

PreviousTeam Member - Add-OnsNextZoho CRM

Last updated