My function is showing this error for a few days, before it worked normally. I really don't know how to insert a fix for this problem. Thank you very much in advance.

Service invoked too many times in a short time: urlfetch. Try Utilities.sleep(1000) between calls.
myTrivia    @ TRIVIA.gs:14

This is my code:

function myTrivia() {
  var f = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('TRIVIA');
  var data = new Date();
  data = Utilities.formatDate(data, "GMT-03:00", "yyyy/MM/dd");
  f.getRange('F1').setValue(data);
  var url = 'https://triviapw.com.br/cotacao/api/item/';
  var [h, ...v] = f.getDataRange().getValues();
  var reqs = v.map(([a]) => ({
    url,
    method: "post",
    payload: {id: a, date: data, server: h[6]},
    muteHttpExceptions: true
  }));
  var res = UrlFetchApp.fetchAll(reqs);
  var values = res.map(r => {
    var str = r.getContentText();
    if (r.getResponseCode() == 200 && str != "Requisição inválida") {
      var obj = JSON.parse(str);
      return [obj.nome, obj.venda, obj.compra];
    }
    return Array(3).fill("");
  });
  f.getRange(2, 2, values.length, values[0].length).setValues(values);
}

After using the code suggested below (by @Tanaike), the system reports the following "Invalid request" and after "Exceeded maximum execution time"

I tested an alternative for just one line and it worked normally (this was the old code)

Here's the code:

function myTrivia() {
  var f = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('TRIVIA');

  
  var formData = {
    'id': f.getRange('A2').getValue(), //A2 IDitem
    'date': f.getRange('F1').getValue(), //F1 Date
    'server': f.getRange('G1').getValue(), //G1 ServerName
  };
  
  var options = {
    'method': 'post',
    'payload': formData
  };
  var result = UrlFetchApp.fetch('https://triviapw.com.br/cotacao/api/item/', options);
  var response = JSON.parse(result.getContentText());
  var res_code = result.getResponseCode();
  if (res_code == 200) {
    f.getRange('B2').setValue(response.nome); //name item
    f.getRange('C2').setValue((response.venda   response.compra) / 2); //midle price for test
  }
}

CodePudding user response：

From So my spreadsheet has 116 rows and I usually update it once or twice a day at most., I thought that in this case, your error message of Service invoked too many times in a short time: urlfetch. might not occur. So, I'm worried about other reasons. But, if you want to use UrlFetchApp.fetch instead of UrlFetchApp.fetchAll, how about the following modified script?

Modified script:

function myTrivia() {
  var f = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('TRIVIA');
  var data = new Date();
  data = Utilities.formatDate(data, "GMT-03:00", "yyyy/MM/dd");
  f.getRange('F1').setValue(data);
  var url = 'https://triviapw.com.br/cotacao/api/item/';
  var [h, ...v] = f.getDataRange().getValues();
  var values = v.map(([a]) => {
    var r = UrlFetchApp.fetch(url, { method: "post", payload: { id: a, date: data, server: h[6] }, muteHttpExceptions: true });
    var str = r.getContentText();
    var res;
    if (r.getResponseCode() == 200 && str != "Requisição inválida") {
      var obj = JSON.parse(str);
      res = [obj.nome, obj.venda, obj.compra];
    } else {
      console.log(str); // If an error occurs, you can see the error message at the log.
      res = Array(3).fill("");
    }
    Utilities.sleep(2000);
    return res;
  });
  f.getRange(2, 2, values.length, values[0].length).setValues(values);
}

• In this modified script, each request is run with the synchronous process. And, each request is run by Utilities.sleep(2000). Please adjust the value of 2000 of Utilities.sleep(2000) for your actual situation.

Reference:

• sleep(milliseconds)

Added:

When you want to use the script for retrieving one value from one row as a custom function, how about the following sample script? In this sample script, please put a custom function like =myTrivia(A2) to a cell. By this, the script is run for one row.

function myTrivia(id) {
  var max = 2; // Number of retry request.
  var [date, server] = SpreadsheetApp.getActiveSheet().getRange("F1:G1").getDisplayValues()[0];
  var retry = 0;
  do {
    var url = 'https://triviapw.com.br/cotacao/api/item/';
    var r = UrlFetchApp.fetch(url, { method: "post", payload: { id, date, server }, muteHttpExceptions: true });
    var str = r.getContentText();
    if (r.getResponseCode() == 200 && str != "Requisição inválida") {
      var obj = JSON.parse(str);
      return [[obj.nome, obj.venda, obj.compra]];
    }
    retry  ;
    Utilities.sleep(2000);
  } while (retry < max);
  return [Array(3).fill("")];
}

• In this sample script, when 1st request occurs an error, the request is retried. Even when 2 requests are done, when an error occurs, the empty value is returned. You can adjust the number of retry with max.