0

I use Google Script in my Google Sheets, to help me shortening url. Below is my script:

function ShortURL(longUrl) {
  const url = "http://example.com/api/url/add";
  const params = {
    method: "post",
    headers: { Authorization: "Token MYTOKEN" },
    contentType: "application/json",
    payload: JSON.stringify({
      "url": longUrl
    })
  };
  const res = UrlFetchApp.fetch(url, params);
  var shortUrl = JSON.parse(res.getContentText()).short;
  Logger.log(shortUrl);
  return shortUrl;}

But I got error when I run it in my google sheets. It just fetch below 100 urls The error message is:

Exception: Service invoked too many times for one day: urlfetch. (line 11).

I read that I can use cache to avoid error but not sure how to use it in my code above

How can I use cache to enhance my script above?

I search in stackoverflow, found some answer here but, since I have no idea to implement it in my script, I still got confuse.

I search that we can use Utilities.sleep() to slow down the script, is that possible?

Got the answer:

So I can use function below:

const min = 1; // milliseconds in one minute
const max = 20000; // milliseconds in 20s since custom function max run time 30s
const waitTime = Math.floor(Math.random() * (max - min + 1) ) + min;
Utilities.sleep(waitTime);

so my code become:

function ShortURL(longUrl) {
  const url = "http://example.com/api/url/add";
  const params = {
    method: "post",
    headers: { Authorization: "Token MYTOKEN" },
    contentType: "application/json",
    payload: JSON.stringify({
      "url": longUrl
    })
  };
  const res = UrlFetchApp.fetch(url, params);
  var shortUrl = JSON.parse(res.getContentText()).short;
  Logger.log(shortUrl);
  const min = 1; // milliseconds in one minute
  const max = 20000; // milliseconds in 20s since custom function max run time 30s
  const waitTime = Math.floor(Math.random() * (max - min + 1) ) + min;
  Utilities.sleep(waitTime);
  return shortUrl;}

And It proven

Thank you :)

Faro
  • 21
  • 6
  • I have read this [post](https://stackoverflow.com/a/65492673/14606046) where it might be caused due to rolling average of service invocations. You might want to try first introducing a delay of 5seconds `Utilities.sleep(5000)` and see if it will fix the issue before we try the cache service – Ron M Jun 21 '21 at 18:31
  • Based on my understanding, cache service was used to prevent frequent url fetch calls for a url. It will wait for the cache to expire before another fetch call will be done – Ron M Jun 21 '21 at 18:34
  • @RonM can you share with me on how to add Utilities.sleep(5000) into my script? or maybe is there any documentation where I can learn it? My coding skill is very basic :( – Faro Jun 22 '21 at 10:35
  • 1
    It sounds like you just need to slow the rate at which calls are made. I don't see how using cache for calls with unique urls will accomplish anything. – Cooper Jun 22 '21 at 13:35
  • 1
    You can just try adding `Utilities.sleep(5000);` before `return shortUrl;`. Reference: [Utilities.sleep(milliseconds)](https://developers.google.com/apps-script/reference/utilities/utilities#sleepmilliseconds) – Ron M Jun 22 '21 at 14:59
  • @RonM Is there any way i can set random time? Such as Utilities.sleep(RANDOM_FROM_1MINS-5MIN); because the calling is made 100 at a time by zapier (automation). So delaying 5 seconds, means just delay all 100 calls. If I can randomize the calling, I think it will be unique time for each of the calling. – Faro Jun 23 '21 at 01:35

0 Answers0