Google Spreadsheet Marcro App Script Adding Delay for The Macro Functions Lists-CodePudding

Hello I have a Macro Record which calling URL fetch function which getting some metadata from the specific URL

For Example i have 12 records in the Macro and want to have like 2 seconds pause after each macro trigger.

function FetchMetaData() {

  var spreadsheet = SpreadsheetApp.getActive();

  spreadsheet.getRange('AV4').activate();
  spreadsheet.getCurrentCell().setFormula('=HtmlLang($G$2)');
  spreadsheet.getRange('AV6').activate();
  spreadsheet.getCurrentCell().setFormula('=MetaCharset($G$2)');
  spreadsheet.getRange('AV9').activate();
  spreadsheet.getCurrentCell().setFormula('=MetaViewport($G$2)');
  spreadsheet.getRange('AV10').activate();
  spreadsheet.getCurrentCell().setFormula('=Title($G$2)');
  spreadsheet.getRange('AV11').activate();
  spreadsheet.getCurrentCell().setFormula('=MetaD($G$2)');
  spreadsheet.getRange('AV15').activate();
  spreadsheet.getCurrentCell().setFormula('=MetaPropertyLocale($G$2)');

};

Where i am suppossed to add the delay function Utillty in the code above

Utilities.sleep(500);

I am trying this because the overall script macros will be to fetch the whole single domain with fecth(url) function and the number of total live checks will be near 200 sepatrate macros i have already in dropdown menus in the spreadsheet. Once i make all macros recorded in a single macro the script above will have loke 200 records. Obviously all of them are using fetch function and running all in one will add huge amount of lag on the spreadsheet and probably some blocks based on the number of urls the site have.

I already try adding the

Utilities.sleep(500);

below the var function but this is not add a delay between fetch runner like

function FetchMetaData() {
  var spreadsheet = SpreadsheetApp.getActive();
  Utilities.sleep(500);

Also try this way

function FetchMetaData() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('AV4').activate();
  Utilities.sleep(3500);
  spreadsheet.getCurrentCell().setFormula('=HtmlLang($G$2)');
  spreadsheet.getRange('AV6').activate();
  Utilities.sleep(3500);
  spreadsheet.getCurrentCell().setFormula('=MetaCharset($G$2)');

};

And this way

function FetchMetaData() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('AV4').activate();
  spreadsheet.getCurrentCell().setFormula('=HtmlLang($G$2)');
  Utilities.sleep(3500);
  spreadsheet.getRange('AV6').activate();
  spreadsheet.getCurrentCell().setFormula('=MetaCharset($G$2)');
  Utilities.sleep(3500);
};

Also between every .activate value but the macro runn all of the functions without delay

Any idea how can i do this in more elegant way to add the delay with a few rows of code. Should i add all Range in Loop and For Each Function to run the Sleep Delay Or Any Help i will Appreciate Thanks

CodePudding user response：

Try it this way:

function FetchMetaData() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const rgA = sh.getRangeList(['AV4','AV6','AV9','AV10','AV11','AV15']).getRanges();
  const fA = ['=HtmlLang($G$2)','=MetaCharset($G$2)','=MetaViewport($G$2)','=Title($G$2)','=MetaD($G$2)','=MetaPropertyLocale($G$2)'];
  rgA.forEach((r,i) => {
    r.setFormula(fA[i]);
     SpreadsheetApp.flush(); 
  });
}