Telegram API Question posted in
A comprehensive official documentation can be found here.

How create a 2-second pause for each formula added in a Auto Fill Down created via Google Script? – Telegram API

I have a custom function to send messages to Telegram from values that are in the cells of my table.

With Auto Fill Down created at the same time, Telegram warns that it has hit the simultaneous sending limit when there are many different cells with values, so I need to find a way to add a 2 second pause for each formula that is added in the cells and do not create a queue for sending.

My script currently looks like this:

  var sheetName = "Sheet4";
  var sss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = sss.getSheetByName(sheetName);
  sheet.getRange('AB1').setFormula('=IF(Z1="","",EnviarTelegram($AA$1,$AA$2,URLredirect(U1),Z1))');
  var lr = sheet.getLastRow();
  var fillDownRange = sheet.getRange(1, 28, lr);
  sheet.getRange('AB1').copyTo(fillDownRange);

Example of my need:

Formula added in AB1
=IF(Z1="","",EnviarTelegram($AA$1,$AA$2,URLredirect(U1),Z1))

2 second pause

Formula added in AB2
=IF(Z2="","",EnviarTelegram($AA$1,$AA$2,URLredirect(U2),Z2))

2 second pause

Formula added in AB3
=IF(Z3="","",EnviarTelegram($AA$1,$AA$2,URLredirect(U3),Z3))

And so on…

Tags:

2

Answers


  1. 0

    I’m not going to build the entire script for you because if I do then you will never learn how to do it by yourself. But this is the basic idea

    const formulas=['formula1','formula2',....];
const ranges=['range1','range2',....];//you will have to figure out how to do this
formulas.forEach((f,i)=>{
  ranges[i].setFormula(f);
  spreadsheetApp.flush();
  Utilities.sleep(delay in milliseconds);
});

    Obvious you need to be aware of your quota limits on script time with regard to running such a function with many array elements. Not intended to be an exact answer.

    Login or Signup to reply.
  2. 0

    Adding a pause to add formulas with a custom function is not a good idea because those formulas will be recalculated when the spreadsheet be opened and also when the custom function parameters change.

    Instead, create a function that call the custom function providing it with the corresponding parameters.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search