# WA Broadcast via Freshchat

{% embed url="<https://youtu.be/EewTM4HKOFo>" %}

{% code title="freshchat" overflow="wrap" lineNumbers="true" %}

```javascript
function sendMessage() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var setupSheet = ss.getSheetByName('setup_freshchat');

  // Fetch sheet names from column H on the "setup_freshchat" sheet
  var sheetNames = setupSheet.getRange('H2:H' + setupSheet.getLastRow()).getValues().flat().filter(String);

  var reportSheet = ss.getSheetByName('wa_blast_report');

  // Fetch template and language settings
  var languageCode = setupSheet.getRange('B1').getValue();
  var headerType = setupSheet.getRange('B2').getValue();
  var customParamsConditions = setupSheet.getRange('B3:B7').getValues().flat();
  var bodyParamsKeys = setupSheet.getRange('C3:C7').getValues().flat();

  // Fetch column letters from setup_freshchat
  var phoneNumberColumn = setupSheet.getRange('O2').getValue();
  var statusColumn = setupSheet.getRange('L2').getValue();
  var templateNameColumn = setupSheet.getRange('M2').getValue();
  var mediaUrlColumn = setupSheet.getRange('N2').getValue();

  // Log the column letters
  Logger.log('Phone Number Column: ' + phoneNumberColumn);
  Logger.log('Status Column: ' + statusColumn);
  Logger.log('Template Name Column: ' + templateNameColumn);
  Logger.log('Media URL Column: ' + mediaUrlColumn);

  // Convert column letters to zero-based indices
  var phoneNumberIndex = letterToColumnIndex(phoneNumberColumn);
  var statusIndex = letterToColumnIndex(statusColumn);
  var templateNameIndex = letterToColumnIndex(templateNameColumn);
  var mediaUrlIndex = letterToColumnIndex(mediaUrlColumn);

  // Log the column indices
  Logger.log('Phone Number Index: ' + phoneNumberIndex);
  Logger.log('Status Index: ' + statusIndex);
  Logger.log('Template Name Index: ' + templateNameIndex);
  Logger.log('Media URL Index: ' + mediaUrlIndex);

  var reportRows = [];

  sheetNames.forEach(function(sheetName) {
    var sheet = ss.getSheetByName(sheetName);
    if (!sheet) return;

    var lastRow = sheet.getLastRow();
    var columns = [phoneNumberIndex, statusIndex, templateNameIndex, mediaUrlIndex];
    var minColumn = Math.min.apply(null, columns) + 1;
    var maxColumn = Math.max.apply(null, columns) + 1;

    // Log the range details
    Logger.log('Sheet Name: ' + sheetName);
    Logger.log('Last Row: ' + lastRow);
    Logger.log('Min Column: ' + minColumn);
    Logger.log('Max Column: ' + maxColumn);

    var dataRange = sheet.getRange(2, minColumn, lastRow - 1, maxColumn - minColumn + 1);
    var dataValues = dataRange.getValues();

    var statusUpdates = dataValues.map(row => [row[statusIndex - minColumn + 1]]);

    dataValues.forEach((row, index) => {
      var phoneNumber = row[phoneNumberIndex - minColumn + 1];
      var status = row[statusIndex - minColumn + 1];
      var templateName = row[templateNameIndex - minColumn + 1];
      var mediaUrl = row[mediaUrlIndex - minColumn + 1];

      if (status === "Ready") {
        try {
          // Construct bodyParams based on "Custom" condition
          var bodyParams = constructBodyParams(bodyParamsKeys, customParamsConditions, index + 2, sheet);

          var payload = {
            "from": { "phone_number": "[YOUR PHONE NUMBER]" },
            "provider": "whatsapp",
            "to": [{ "phone_number": "+" + String(phoneNumber).trim() }],
            "data": {
              "message_template": {
                "storage": "none",
                "template_name": templateName,
                "namespace": "[YOUR NAMESPACE]",
                "language": {
                  "policy": "deterministic",
                  "code": languageCode
                },
                "rich_template_data": {}
              }
            }
          };

          // Add header only if headerType is not empty
          if (headerType) {
            payload.data.message_template.rich_template_data.header = {
              "type": headerType,
              "media_url": mediaUrl
            };
          }

          // Add body params only if they exist
          if (bodyParams.length > 0) {
            payload.data.message_template.rich_template_data.body = {
              "params": bodyParams
            };
          }

          var options = {
            "method": "post",
            "contentType": "application/json",
            "headers": {
              "Authorization": "[YOUR API KEY]"
            },
            "payload": JSON.stringify(payload),
            "muteHttpExceptions": true
          };

          var response = UrlFetchApp.fetch("https://api.freshchat.com/v2/outbound-messages/whatsapp", options);
          var result = JSON.parse(response.getContentText());

          Logger.log(result); // Log the full result to understand the response structure

          if (result.request_id) {
            reportRows.push([new Date(), phoneNumber, templateName, "Success"]);
            statusUpdates[index] = ["Done"];
          } else {
            reportRows.push([new Date(), phoneNumber, templateName, "Failed"]);
            statusUpdates[index] = ["Failed"];
            Logger.log("Error: " + result.error_message || "Undefined error message");
          }
        } catch (e) {
          reportRows.push([new Date(), phoneNumber, templateName, "Failed"]);
          statusUpdates[index] = ["Failed"];
          Logger.log("Error: " + e.toString());
        }
      }
    });

    if (reportRows.length > 0) {
      reportSheet.getRange(reportSheet.getLastRow() + 1, 1, reportRows.length, 4).setValues(reportRows);
    }

    sheet.getRange('I2:I' + lastRow).setValues(statusUpdates);
  });
}

function constructBodyParams(bodyParamsKeys, customParamsConditions, rowIndex, sheet) {
  var bodyParams = [];
  bodyParamsKeys.forEach((key, index) => {
    if (customParamsConditions[index] === "Custom") {
      bodyParams.push({ "data": key });
    } else {
      var columnLetter = customParamsConditions[index].split(' ')[1]; // Extracts column letter
      if(columnLetter) {
        try {
          var value = sheet.getRange(columnLetter + rowIndex).getValue();
          if (value) {
            bodyParams.push({ "data": value });
          }
        } catch(e) {
          Logger.log('Error accessing cell for column ' + columnLetter + ' in row ' + rowIndex + ': ' + e.toString());
        }
      }
    }
  });
  return bodyParams;
}

function letterToColumnIndex(letter) {
  var columnIndex = 0;
  var letters = letter.replace('Column ', '').toUpperCase();
  for (var i = 0; i < letters.length; i++) {
    columnIndex = columnIndex * 26 + (letters.charCodeAt(i) - 'A'.charCodeAt(0)) + 1;
  }
  return columnIndex - 1; // Convert to zero-based index
}
```

{% endcode %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://work.bahrul.me/work/kyzn/crm/wa-broadcast-via-freshchat.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.