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