CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Digital Farmer
Digital Farmer

Reputation: 2127

Use ARRAYFORMULA in a custom script function (Google Sheets + GAS)

Is there any way to adjust this function for use with ARRAYFORMULA in spreadsheets?

function SendTelegram(botSecret, chatId, body) {
  var response = UrlFetchApp.fetch("https://api.telegram.org/bot" + botSecret + "/sendMessage?text=" + encodeURIComponent(body) + "&chat_id=" + chatId + "&parse_mode=HTML");
}

Instead of using a formula for each line like this:

=SendTelegram($H$1,$I$1,F2)
=SendTelegram($H$1,$I$1,F3)
=SendTelegram($H$1,$I$1,F4)
=SendTelegram($H$1,$I$1,F5)

By using this way:

=ARRAYFORMULA(SendTelegram($H$1,$I$1,F2:F))

Returns the following message:

Limit Exceeded: URLFetch URL Length. (line 22).

Upvotes: 1

Views: 194

Answers (1)

CodeCamper
CodeCamper

Reputation: 6984

Something like this, you might have to adjust to the particular behavior you want.

function SendTelegram(botSecret, chatId, body) {
  if (body.map) { // is array?
  var response = body.map(function(b) {return SendTelegram(botSecret,chatId,b);});
   } else {
  var response = UrlFetchApp.fetch("https://api.telegram.org/bot" + botSecret + "/sendMessage?text=" + encodeURIComponent(body) + "&chat_id=" + chatId + "&parse_mode=HTML");
  }
}

Upvotes: 2

Related Questions