0

I'm working on a google sheet for a calculator for a game. I have this script that automatically updates the price of a game item and I grab the API using URLFetch. However, every time I go to sleep overnight, and I wake up, the calculator is always broken because I reached my daily limit of URLFetch calls? I've been trying to fix this for a while I can't. Maybe someone could look at it to reduce the amount of calls?

I set a trigger on it to update every 10 minutes. This is because if I set the trigger to update every 15 minutes, there is a #NAME error that pops up.

Here is the code:

var sheetData = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("data");
var sheetItems = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Items");
var sheetSettings = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Settings");

var lastPrices = {};

var settings = {
  "update_step": 1,
  "update_times": 1,
  "timezone": "GMT"
};

function onOpen() {
  var ui = SpreadsheetApp.getUi();

    ui.createMenu("Update")
    .addItem("settings", "updateSettings")
    .addItem("next", "updateNext")
    .addToUi();
}

function updateSettings() {
  var values = sheetSettings.getRange("B2:B6").getValues();

  settings.update_step = values[0];
  settings.update_times = values[2];
  settings.timezone = values[3];
}

function updateNext() {
  updateSettings();

  var current = parseInt(PropertiesService.getScriptProperties().getProperty("currentUpdate"));
  if (!current) current = 1;

  update(1 + ((current - 1) * settings.update_step), current * settings.update_step);

  current++;
  if (current > settings.update_times) current = 1;

  PropertiesService.getScriptProperties().setProperty("currentUpdate", current);
}

function update(from, to) {
  sheetData.getRange("B" + 1 + ":B" + 171).setValue(Utilities.formatDate(new Date(), settings.timezone, "HH:mm:ss dd/MM/yyyy"))
}

function getBazaarPrice(api, item, place) {
  var json = JSON.parse(UrlFetchApp.fetch("https://api.torn.com/market/" + item + "?selections=bazaar&key=" + api));

  var price, message;
  if (json.bazaar != null) {
    var i = 1;
    for (var id in json.bazaar) {
      if(i == place) {
        price = json.bazaar[id].cost;
        CacheService.getScriptCache().put(item, price);
        break;
      }
      i++;
    }
  }  else {
    price = CacheService.getScriptCache().get(item);
    if (price == null) {
      price = 0;
    } else {
      price = parseInt(price); 
    }
    message = "ERROR (" + json.error.code + ")";
  }

  return [[price, message]];
}

Line 49, specifically: var json = JSON.parse(UrlFetchApp.fetch("https://api.torn.com/market/" + item + "?selections=bazaar&key=" + api));

is always where the URLFetch error occurs. Thank you! :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.