Search code examples
google-apps-scriptgoogle-sheetsgoogle-chat

Sending Message from G Sheet to Google Chat / Spaces

This code works for sending text messages to Spaces but when I would like to send from the spreadsheet where I have added the app Script. The code I am using is:


function myFunction() {
    var WebWhooklink = "Sample_URL"
    var message = { text: "Hello"};
    var payload = JSON.stringify(message);
    var options = {
            method: 'POST',
            contentType: 'application/json',
            payload: payload
    };
  
    var response =  UrlFetchApp.fetch(WebWhooklink, options ).getContentText();
}

I have modified to below but the error shows up as follows:

function myFunction() {
 var sheet = SpreadsheetApp.getActive().getSheetByName("Email");
var dataGrid = sheet.getRange(3, 2, 3, 3).getValues();  


    var WebWhooklink = "Sample_URL"
    var message = {dataGrid};
    var payload = JSON.stringify(message);
    var options = {
            method: 'POST',
            contentType: 'application/json',
            payload: payload
    };
  
    var response =  UrlFetchApp.fetch(WebWhooklink, options ).getContentText();
}

Error that comes:

Exception: Request failed for https://chat.googleapis.com returned >code 400. Truncated server response: { "error": { "code": 400,

For Reference my Spreadsheet name is "Emailer" and range is as mentioned above and that is what I would like to send. I have tried multiple methods but it doesn't seem to send that range in the chat.

Attached is the spreadsheet link

Solution

  • The full error appears to indicate an issue with the message object:

    "message": "Invalid JSON payload received. Unknown name "dataGrid" at 'message': Cannot find field."

    The documentation for this object indicates that it needs to include specific properties, for example, you can use text to send the values by passing dataGrid.toString():

    function myFunction() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Email");
  var dataGrid = sheet.getRange(3, 2, 3, 3).getValues();

  var WebWhooklink = "Sample_URL"
  var message = { text: dataGrid.toString() }; // <- here
  var payload = JSON.stringify(message);
  var options = {
    method: 'POST',
    contentType: 'application/json',
    payload: payload
  };

  var response = UrlFetchApp.fetch(WebWhooklink, options).getContentText();
}

    This object also provides more options like using Cards.

    Reference: