From Google Sheets
This is a step by step guide on how to trigger notifications from Google Sheets.
Pre-requisites
You may have data coming into Google Sheets from variety of sources, it could be data from your own database, or data coming in from no-code /low code tools. If you want to trigger notification on this data, you will need following in place on SuprSend account:
- Vendors setup for the channels on which you want to trigger notifications
- Templates published for the channels
Steps
-
Create a template group on SuprSend account. All the static content can be designed on the template, and all the variables to be defined within {{...}}. These variables will be passed from the Google Sheet at the time of trigger. You can read more on how to create template and define variables from here.
-
Create a google sheet with all the data you would want to send as dynamic content (aka variables) as Columns of the sheet. Note that variable names are case sensitive.
In the example below, name, EventID, Event, Schedule are the variables name that we have defined in the template. Columnname
should have a corresponding variable in the template defined as{{name}}

name, EventID, Event, Schedule are variables defined the template
- Make some columns that will have the data on users channels details. In the example above, WA refers to user channel details of Whatsapp, Email refers to user channel details of Email. For Whatsapp, you need to enter country code infront of the mobile number in + format. Eg. +917123xxxxxx
Tip
Google Sheet doesn't allow to start a field with
+
. To enter in + format, use string function:="+917123xxxxxx"
-
Introduce a new column in the sheet, called
SuprSend Status
. Fill the valueTBT
for rows for which you want to trigger notification. -
In the Navbar of Google Sheets, go to Extensions and select Apps Script
- It will open Apps Script in a new tab. Remove the default information present in the editor, and copy-paste the following in the editor.
//Enter your workspace key, secret, template slug, workflow name & category
const workspace_key = "__WORKSPACE_KEY__";
const workspace_secret = "__WORKSPACE_SECRET__";
const template_slug = "__TEMPLATE_SLUG__";
const workflow_name = "__WORKFLOW_NAME__";
const category = "transactional"
// Map your column names to channels if need be
// Or ensure you use following names for your columns to directly map them to channels
// distinc_id for user's distinct id
// $sms for user's mobile number
// $email for user's email
// $whatsapp for user's whatsapp
// If you have other names of your columns you can modify following two lines accordingly
const channel_col_names = {"WA":"$whatsapp","Email":"$email","SMS":"$sms"};
const distinct_id_col_name = 'distinct_id'
//--------- No Editing required below -----------------//
function Trigger_Workflows() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
var headers = data[0];
for (var i = 1; i < data.length; i++) {
var response = convert_row_to_payload(data[i], headers);
if (response.status === "TBT") {
make_api_request(
response.payload,
sheet.getRange(i + 1, parseInt(response.status_col) + 1)
);
}
}
}
function convert_row_to_payload(data, headers) {
let status = "";
let status_col = -1;
let user = {
distinct_id: null,
$email: [],
$sms: [],
$whatsapp: [],
};
let payload = {};
payload.data = {};
let private_channelkeys = ["$sms", "$whatsapp", "$email", "distinct_id"];
for (var i = 0; i < headers.length; i++) {
if (data[i].length !== 0) {
if (
channel_col_names[headers[i]] ||
private_channelkeys.includes(headers[i])
) {
if (headers[i] !== "distinct_id") {
if (user[channel_col_names[headers[i]]])
user[channel_col_names[headers[i]]].push(data[i]);
if (user[headers[i]]) user[headers[i]].push(data[i]);
} else {
user[headers[i]] = data[i];
}
}
if (headers[i] !== "SuprSend Status") {
payload.data[headers[i]] = data[i];
} else {
status = data[i];
status_col = i;
}
}
}
user["distinct_id"] = payload.data[distinct_id_col_name];
//user["is_transient"] = true; //Uncomment if user is temporary
payload.users = [user];
payload.name = workflow_name;
payload.notification_category = category;
payload.template = template_slug;
return {
payload: JSON.stringify(payload),
status: status,
status_col: status_col,
};
}
function make_api_request(payload, cell) {
const uri = "/" + workspace_key + "/trigger/";
const url = "https://hub.suprsend.com" + uri;
const md5 = MD5(payload);
const now = new Date().toISOString();
const message =
"POST" + "\n" + md5 + "\n" + "application/json" + "\n" + now + "\n" + uri;
const byteSignature = Utilities.computeHmacSha256Signature(
message,
workspace_secret
);
const signature = Utilities.base64Encode(byteSignature);
var options = {
method: "POST",
contentType: "application/json",
headers: {
Authorization: workspace_key + ":" + signature,
Date: now,
},
payload: payload,
muteHttpExceptions: true,
};
cell.setValue("Processing...");
try {
var response = UrlFetchApp.fetch(url, options);
cell.setValue(response.getContentText());
} catch (error) {
cell.setValue("Error : " + error);
}
}
function onOpen() {
var ui = SpreadsheetApp.getUi();
// Or DocumentApp or FormApp.
ui.createMenu("SuprSend")
.addItem("Trigger SuprSend Workflow", "Trigger_Workflows")
.addToUi();
}
function MD5(input, isShortMode) {
var isShortMode = !!isShortMode; // Be sure to be bool
var txtHash = "";
var rawHash = Utilities.computeDigest(
Utilities.DigestAlgorithm.MD5,
input,
Utilities.Charset.UTF_8
);
if (!isShortMode) {
for (i = 0; i < rawHash.length; i++) {
var hashVal = rawHash[i];
if (hashVal < 0) {
hashVal += 256;
}
if (hashVal.toString(16).length == 1) {
txtHash += "0";
}
txtHash += hashVal.toString(16);
}
} else {
for (j = 0; j < 16; j += 8) {
hashVal =
(rawHash[j] + rawHash[j + 1] + rawHash[j + 2] + rawHash[j + 3]) ^
(rawHash[j + 4] + rawHash[j + 5] + rawHash[j + 6] + rawHash[j + 7]);
if (hashVal < 0) {
hashVal += 1024;
}
if (hashVal.toString(36).length == 1) {
txtHash += "0";
}
txtHash += hashVal.toString(36);
}
}
// change below to "txtHash.toUpperCase()" if needed
return txtHash;
}
- You need to add following information in the script which is related to your account:
Data | Description |
---|---|
workspace-key | Add the workspace key from SuprSend account. You will find it in the left navigation panel. You can read more information on Workspaces here. |
workspace-secret | Add the workspace secret from SuprSend account. You wll find it in the left navigation panel. You can read more information on Workspaces here. |
template-slug-name | Add the template slug name of the template that you want to trigger. You will find the template slug name from the template details page. You can read more information on Template Slug Name here. |
Workflow Name | Give a Workflow name to your notification. |
category | Provide notification category, default values are: System / Transactional / Promotional You can create your own custom categories in SuprSend account, you can read more Notification Categories here. |
channel_col_names | This is where you link your column names to channels. You need to define it in the following format:const channel_col_names = {"WA":"$whatsapp"}; WA refers to the column name in the sheet which has information stored on user's Whatsapp numbers.$whatsapp refers to how SuprSend identifies Whatsapp number.To map email, use $email To map android push, use $androidpush To map android push, use $webpush |
- Save the Script, and close the tab. Reload your Google Sheets, and you will find a new option on the navigation bar, called "SuprSend". On clicking it, you will see option of "Trigger SuprSend Workflow".
On triggering, the script will pick up all the rows which have value TBT
in the column name "SuprSend Status", and will make an API call to SuprSend. For the successful API call, the status will change to OK
.
- You can check the status of your notification trigger from the Logs page on SuprSend.
Updated 10 months ago