This article demonstrate how you can send WhatsApp message from google sheet and once the message is submitted to Picky Assist then update a value back to the google sheet specified column.

This script is mainly used to solve following cases;

1. If the google sheet is updated from any other 3rd party applications like Zapier or any update which are not performed by a user then trigger a WhatsApp message. (Google Scripts which we have published will work only if the value is updated by a human)
2. Sending bulk messages (30 messages per minute) from google sheet based on certain values becomes true, for example from a list of records you want to send messages only to few selected records

Google Script

Make Changes for the values which are shown under “CONFIG”

function Picky_Scheduled_Alert() {

/***-- CONFIG --***/

	var TargetColumn = 'J';

	var SheetName = '';  

	var Trigger_value = '';  

	var Final_value = 'DONE'; 

        var TimeStamp_Column = '';

	var Triger_name = 'Scheduled_Alert';

	var Triger_url = 'https://webhook.site/44757124-5c2a-4c41-a63f-7a138b914ce8';

/****************************/


	if( TargetColumn == '' || Triger_url == '')
		return false;

	var tarCol = 0, length = TargetColumn.length;

	for (var i = 0; i < length; i++)
	{
		tarCol += (TargetColumn.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
	}

  var TimeStamp_Col = -1;

  var time_stamp = '';

  if( TimeStamp_Column != '' ){

    	var TimeStamp_Col = 0, length = TimeStamp_Column.length;

      for (var i = 0; i < length; i++)
      {
        TimeStamp_Col += (TimeStamp_Column.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
      }


      var timeZone = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();

      var tarDate = new Date();

      time_stamp  = tarDate.toLocaleString("en-US", {timeZone: timeZone})

  }


	if( SheetName == '')
		var sheet = SpreadsheetApp.getActive().getSheets()[0];
	else
		var sheet = SpreadsheetApp.getActive().getSheetByName(SheetName);
	

	var startRow = 1;
	var lastRow = sheet.getLastRow();
	var lastCloumn = sheet.getLastColumn()


	for (var i =startRow ; i <= lastRow; i++) {

		var c_val = sheet.getRange(i, tarCol).getValue();
		//Logger.log(c_val);

		try{

			if( c_val == Trigger_value ){

				Logger.log("TRIGGER VALUE FOUND:", c_val);

				

				var formData = {
						'triger_name': Triger_name
				};

				var range1 = sheet.getRange(i, 1, 1, lastCloumn);
				var values = range1.getDisplayValues();

				for (var j = 0; j < sheet.getLastColumn(); j++){

					var temp, letter = '';

					var column = j+1;

					while (column > 0) {
						temp = (column - 1) % 26;
						letter = String.fromCharCode(temp + 65) + letter;
						column = (column - temp - 1) / 26;
					}

					formData[letter] = values[0][j];

				}

				var options = {
						'method' : 'post',
						'payload' : formData
					};

				UrlFetchApp.fetch(Triger_url, options);
				delete formData;


				sheet.getRange(i, tarCol).setValue(Final_value);


        if( TimeStamp_Col > -1 ){
          sheet.getRange(i, TimeStamp_Col).setValue(time_stamp);
        }

			}

		}catch(err) {
			continue;
		}
		
	} 
	
}

Now we need to update the values from line number 5 to 17 as per different requirements, below are the descriptions

See the below areas i.e 5 to 15 lines where you need to make changes as per your requirements.

Setup Trigger in Google Form

The next step is to Configure the Trigger in the Google Sheet so that every time google will trigger the code as per the schedule you configured,  to do this select the trigger menu from the left side menu bar and then click on “Add Trigger” then configure it as seen in the below screenshot;

After that click on “Save” button and that’s it 

Testing the Integration 

Now to setup the integration we need to map the Google Sheet data with Picky Assist Connector so that it can perform conditions and actions based on the data received from the Google Sheet.

Open the Google Sheet and change the value in the targeted column, then go to Script Editor from Extensions -> App Scripts and click on “Run Icon” to run the script as shown below;

Go to your Picky Assist Account then navigate to Connector, select the connector which was created for this Google Sheet then in the left sidebar you will able to see logs, this means Google notified the Picky Assist for the new update in the column.

Mapping the JSON Data Structure

In order to map the JSON data structure go to “Field Mapping” then click on JSON Code from the popup you will able to see something shown as below;

If you are not seeing this option then please click on the “Code” tab then Click on “Map” button as shown below

Creating Steps

Now we need to create steps which take different action based on the value in the google sheet, in order to do this first let’s create one step with all necessary filter and action then duplicate the steps for ease the process,

Once a step is configured, we can simply take duplicates of the steps and change the filter condition value and message content