App Script - Creating an installable onEdit trigger to call a function that makes an API request - not working

Question

So I'm relatively new to app-script and have succeeded in making an API request using urlFetchApp and pulling data onto my google sheet. Now what I am interested in doing is having this API request code run whenever I edit the contents of a specific cell. And the contents of the cell will be integrated into the request query. To be more specific, I am pulling company financial reports via an API and I want to be able to enter a new company ticker in a cell and have that company's financials immediately pulled to my sheet by way of the API request.

I understand (from experience) that the simple trigger onEdit(e) does not work due to permission issues, and that I need to create an installable trigger. But for some reason, even though the UrlFetchApp works perfectly when run from the script editor, it fails to pull the same data when triggered by the installable trigger I created. Any advice as to how to get this to work and what I am doing wrong will be very much appreciated.

This is my code:

// make an API request and pull the historical income statements 
// for the company ticker in cell B1 of my sheet
function getIncomeStatement() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var url = 'https://www.alphavantage.co/query?function=INCOME_STATEMENT&symbol='
    + SpreadsheetApp.getActiveSheet().getRange('B1').getValue()
    + '&apikey=*****************';
  var response = UrlFetchApp.fetch(url);
  var financials = JSON.parse(response.getContentText());
  return financials;
}

// get the company's ticker and historic annual income statement reports 
// and print them to the active sheet
function getKeysVals() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var financials = getIncomeStatement();

  // get ticker and paste to sheet
  var symbol = financials['symbol'];
  rgMyRange = sheet.getRange(2, 1, 1, 1);
  rgMyRange.setValue('Requested Ticker');
  rgMyRange = sheet.getRange(2, 2, 1, 1);
  rgMyRange.setValue(symbol);

  // get Income Statement annual reports
  var annualReport = financials['annualReports'];

  // loop over nested objects in annualReports
  colToPaste = 1;
  for (var i = 1; i < annualReport.length; i++) {
    yearKeys = [];
    yearValues = [];

    // loop over keys/values within a specific year and paste keys/values to arrays
    var currentYear = annualReport[i];
    for (var key in currentYear) {
      yearKeys.push(key);
      yearValues.push(currentYear[key]);
    }

    // Combine the 2 arrays into one 2-Dimensional array and paste to sheet
    var values = yearKeys.map(function (e, i) { return [e, yearValues[i]] });
    rgMyRange = sheet.getRange(3, colToPaste, values.length, values[0].length);
    rgMyRange.setValues(values);

    // Move the range by 2 columns to the right avoid overwriting the next year's figures 
    colToPaste = colToPaste + 3;
  }
}

// create an installable trigger for onEdit
function createSpreadsheetEditTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('newOnEdit')
      .forSpreadsheet(ss)
      .onEdit()
      .create();
}

// create a new onEdit function to look for changes in cell B1
function newOnEdit(e) {
  var cellAddress = e.range.getA1Notation();
  if (cellAddress === 'B1') {
    getKeysVals();
  }
}

Answer 1

The API is rate limited with burst protection

For many APIs, when they say, for example:

up to 5 API requests per minute

What this actually means is that you can only make one request every 12 seconds. You can't make 5 requests in 10 seconds and then wait for 50 seconds to make another 5 requests.

I am not sure why some APIs are not explicit about this in their documentation, because it is easy to assume that if they say 5 requests per minute, that you can make 5 requests in 0.5 seconds if you like.

A possible reason for this is that it serves as protection against DDOS attacks. Imagine if someone obtained thousands free API keys, and then set up to coordinate each API key to simultaneously send 5 requests. This could break the server.

Workaround

Aside from getting premium membership, you could set a time driven trigger to send a request every 12 seconds to check if there are any stock prices that need checking, and that way, fill them out bit by bit. If it finds no stocks, then it doesn't make a request.

Or you could try an onEdit trigger that stores times with the PropertiesService

function onEdit(e) {
  // Get current time of edit
  let time = new Date();
  // Get the time of last request
  let scriptProperties = PropertiesService.getScriptProperties()
  let property = scriptProperties.getProperty("lastRequest")
  let lastRequest = new Date(property)

  // Calculate time since last request
  let timeSinceLastRequest = time.getTime() - lastRequest.getTime()

  // If more than 12 seconds ago, make request and reassign property
  if (timeSinceLastRequest > 12000) {

    // MAKE THE REQUEST HERE

    scriptProperties.setProperty("lastRequest", new Date())
  }
}


// Run this first to set the property for the first time.
function init(){
  let scriptProperties = PropertiesService.getScriptProperties()
  scriptProperties.setProperty("lastRequest", new Date())
}

References

• Triggers
• Properties Service