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

Question

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

Are you asking to modify it or asking how to use it?

CodeCamper
– CodeCamper

2019-11-24 03:53:44 +00:00
Commented Nov 24, 2019 at 3:53 — CodeCamper
– CodeCamper, Commented Nov 24, 2019 at 3:53
Guidelines for Custom Functions

Cooper
– Cooper

2019-11-24 04:36:13 +00:00
Commented Nov 24, 2019 at 4:36 — Cooper
– Cooper, Commented Nov 24, 2019 at 4:36

Wai Ha Lee · Accepted Answer · 2019-11-24 12:27:11Z

2

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");
  }
}

edited Nov 24, 2019 at 12:27

Wai Ha Lee

8,856102 gold badges61 silver badges100 bronze badges

answered Nov 24, 2019 at 4:47

CodeCamper

7,0148 gold badges49 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related