Business
Jobs
  • About Us
  • Solutions
    • Job Postings
      Post your job and receive qualified candidates in 48h.
    • Candidate Assessments
      500+ technical and psychological tests, plus anti-fraud.
    • Headhunting
      Tailor-made executive search from start to finish.
    • Payroll + EOR
      Payroll dispersal and EOR across 15+ LATAM countries.
  • Pricing
  • Jobs

0

239
Views
Using Google Sheets script to check a cache sheet for each key

I am pulling stock market data from Yahoo! Finance. For data that does not change often, I do not want to contact Yahoo! each time, but source data from a db sheet that acts as a cache:

  1. yahoofinance() is called per ticker from 'Dashboard' sheet
  2. yahoofinance() calls db_data to see if data for ticker is available in db

I have an example sheet here.

function yahoofinance(ticker) {
  if (db_data(ticker)) {
    return get_db_data(ticker);
  }
  else {
    return get_live_data(ticker);
  }
}


function get_live_data(ticker) {
  const url = 'https://query2.finance.yahoo.com/v10/finance/quoteSummary/' + encodeURI(ticker) + '?modules=price,assetProfile,summaryDetail';
  
  let response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
  if (response.getResponseCode() == 200) {
      var object = JSON.parse(response.getContentText());
  }

  let fwdPE  = object.quoteSummary.result[0]?.summaryDetail?.forwardPE?.fmt || '-';
  let sector = object.quoteSummary.result[0]?.assetProfile?.sector || '-';
  let mktCap = object.quoteSummary.result[0]?.price?.marketCap?.fmt || '-';

  return [[fwdPE, sector, mktCap]];
}


function db_data(key) {
  var db =  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('db'); 
  var tickers = db.getDataRange('A2:A').getValues();

  for (var r = 0; r <= tickers.length; r++) { 
    if (tickers[r] == key) {
      return true // found a row with this key
    }
    else {
      return false // no row with this key exists
    }
  }
}

Issue: db_data() does not find ticker AAPL (see sheet Dashboard) even though it is present in sheet db so the matching in db_data() does not work. What am I doing wrong?

about 4 years ago · Juan Pablo Isaza
1 answers
Answer question

0

A working version of the script with the changes suggested in the comments:

function yahoofinance(ticker) {
  if (db_data(ticker)) {
    return db_data(ticker);
  }
  else {
    return get_live_data(ticker);
  }
}


function get_live_data(ticker) {
  const url = 'https://query2.finance.yahoo.com/v10/finance/quoteSummary/' + encodeURI(ticker) + '?modules=price,assetProfile,summaryDetail';
  
  let response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
  if (response.getResponseCode() == 200) {
      var object = JSON.parse(response.getContentText());
  }

  let fwdPE  = object.quoteSummary.result[0]?.summaryDetail?.forwardPE?.fmt || '-';
  let sector = object.quoteSummary.result[0]?.assetProfile?.sector || '-';
  let mktCap = object.quoteSummary.result[0]?.price?.marketCap?.fmt || '-';

  return [[fwdPE, sector, mktCap]];
}


function db_data(key) {
  var db =  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('db'); 
  var tickers = db.getRange('A2:A').getValues();

  for (var r = 0; r <= tickers.length; r++) { 
    if (tickers[r] == key) {
      return true // found a row with this key
    }
    else {
      return false // no row with this key exists
    }
  }
}

Results:

Results

about 4 years ago · Juan Pablo Isaza Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Sales
Legal
Terms and conditions Privacy policy
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Show me some job opportunities
There's an error!