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:

  1. Vendors setup for the channels on which you want to trigger notifications
  2. Templates published for the channels

Steps

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

  2. 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. Column name should have a corresponding variable in the template defined as {{name}}

2750

name, EventID, Event, Schedule are variables defined the template

  1. 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"

  1. Introduce a new column in the sheet, called SuprSend Status. Fill the value TBT for rows for which you want to trigger notification.

  2. In the Navbar of Google Sheets, go to Extensions and select Apps Script


  1. 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;
}


  1. You need to add following information in the script which is related to your account:
DataDescription
workspace-keyAdd the workspace key from SuprSend account. You will find it in the left navigation panel. You can read more information on Workspaces here.
workspace-secretAdd 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-nameAdd 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 NameGive a Workflow name to your notification.
categoryProvide 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_namesThis 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

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

  1. You can check the status of your notification trigger from the Logs page on SuprSend.