Home > database >  Google script says variable has been previously declared but I do not see where
Google script says variable has been previously declared but I do not see where

Time:06-07

I have been pulling company data from Yahoo! Finance, first for only a few stocks, but currently for many more. I am pulling this data in batches, as to not exceed the Google Sheets URLfetch rate limit. The below function trigger() (thanks to help from @Tanaike for an original example) is time-triggered, and pulls data for 5 stocks each time by calling yahoo().

Here is the example Google Sheet, and as you can see:

  • The first column has the stock ticker
  • Columns B and C are empty (because I will later fill in data manually). These columns should not be overwritten
  • Data that gets pulled by yahoo() should be filled in in columns D to AO

I get an error however: trigger(): Syntax error: Identifier 'r' has already been declared line: 10 file: Code.gs

Line 10 is the line from trigger() (see below) that starts with const [ticker, b, c,...

Issue: I do not understand why I get this error. As far as I see, z has not been declared before. Who can help identify what goes wrong and show me how to fix it? Any help would be greatly appreciated!

function trigger() {
  const max = 5; // From your question, maximum execution of "yahoo" is 5.

  const todayObj = new Date();
  const today = Utilities.formatDate(todayObj, Session.getScriptTimeZone(), "yyyyMMdd");
  const db = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('db2');
  const range = db.getRange('A2:AO'   db.getLastRow());

  const { values } = range.getValues().reduce((zo, zr) => {
    const [ticker, b, c, d, e, f, g, h, i, j, k, l, m, n, r, o, p, q, r, s, t, u, v, w, x, y, z, aa, ab, ac, ad, ae, af, ag, ah, ai, aj, ak, al, am, an, ao] = zr;
    if (zo.zc < max && (g.toString() == "" || Utilities.formatDate(an, Session.getScriptTimeZone(), "yyyyMMdd") != today)) {
      try {
        zo.zc  ;
        zo.values.push([ticker, b, c, ...yahoo(ticker), todayObj, null]);
      } catch (_) {
        zo.values.push([ticker, b, c, d, e, f, g, h, i, j, k, l, m, n, r, o, p, q, r, s, t, u, v, w, x, y, z, aa, ab, ac, ad, ae, af, ag, ah, ai, aj, ak, al, am, todayObj, ["", "0"].includes(an.toString()) ? 1 : ao   1]);
      }
    } else {
      zo.values.push(zr);
    }
    return zo;
  }, { values: [], zc: 0 });
  range.setValues(values);
}


function yahoo(ticker) {
  const url = 'https://query2.finance.yahoo.com/v10/finance/quoteSummary/'   encodeURI(ticker)   '?modules=summaryDetail,financialData,defaultKeyStatistics';

  let response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
  if (response.getResponseCode() == 200) {
    var object = JSON.parse(response.getContentText());
  }

  // misc
  let marketCap               = object.quoteSummary.result[0]?.summaryDetail?.marketCap?.raw                       || '-';
  let dividendRate            = object.quoteSummary.result[0]?.summaryDetail?.dividendRate?.raw                    || '-';
  let dividendYield           = object.quoteSummary.result[0]?.summaryDetail?.dividendYield?.raw                   || '-';
  let payoutRatio             = object.quoteSummary.result[0]?.summaryDetail?.payoutRatio?.raw                     || '-';
  let fiveYAvgDivYield        = object.quoteSummary.result[0]?.summaryDetail?.fiveYearAvgDividendYield?.raw        || '-';
  let insidersPercentHeld     = object.quoteSummary.result[0]?.majorHoldersBreakdown?.insidersPercentHeld?.raw     || '-';
  let institutionsPercentHeld = object.quoteSummary.result[0]?.majorHoldersBreakdown?.institutionsPercentHeld?.raw || '-';
  
  // dates
  let earningsDate            = object.quoteSummary.result[0]?.calendarEvents?.earnings?.earningsDate[0]?.raw      || '-';
  let exDividendDate          = object.quoteSummary.result[0]?.calendarEvents?.exDividendDate?.raw                 || '-';
  let dividendDate            = object.quoteSummary.result[0]?.calendarEvents?.dividendDate?.raw                   || '-';

  // earnings
  let totalRevenue            = object.quoteSummary.result[0]?.financialData?.totalRevenue?.raw                    || '-';
  let revenueGrowth           = object.quoteSummary.result[0]?.financialData?.revenueGrowth?.raw                   || '-';
  let revenuePerShare         = object.quoteSummary.result[0]?.financialData?.revenuePerShare?.raw                 || '-';
  let ebitda                  = object.quoteSummary.result[0]?.financialData?.ebitda?.raw                          || '-';
  let grossProfits            = object.quoteSummary.result[0]?.financialData?.grossProfits?.raw                    || '-';
  let earningsGrowth          = object.quoteSummary.result[0]?.financialData?.earningsGrowth?.raw                  || '-';
  let grossMargins            = object.quoteSummary.result[0]?.financialData?.grossMargins?.raw                    || '-';
  let ebitdaMargins           = object.quoteSummary.result[0]?.financialData?.ebitdaMargins?.raw                   || '-';
  let operatingMargins        = object.quoteSummary.result[0]?.financialData?.operatingMargins?.raw                || '-';
  let profitMargins           = object.quoteSummary.result[0]?.financialData?.profitMargins?.raw                   || '-';

  // cash
  let totalCash               = object.quoteSummary.result[0]?.financialData?.totalCash?.raw                       || '-';
  let freeCashflow            = object.quoteSummary.result[0]?.financialData?.freeCashflow?.raw                    || '-';
  let opCashflow              = object.quoteSummary.result[0]?.financialData?.operatingCashflow?.raw               || '-';
  let cashPerShare            = object.quoteSummary.result[0]?.financialData?.totalCashPerShare?.raw               || '-';

  // debt
  let totalDebt               = object.quoteSummary.result[0]?.financialData?.totalDebt?.raw                       || '-';
  let debtToEquity            = object.quoteSummary.result[0]?.financialData?.debtToEquity?.raw                    || '-';

  // ratios
  let quickRatio              = object.quoteSummary.result[0]?.financialData?.quickRatio?.raw                      || '-';
  let currentRatio            = object.quoteSummary.result[0]?.financialData?.currentRatio?.raw                    || '-';
  let trailingEps             = object.quoteSummary.result[0]?.defaultKeyStatistics?.trailingEps?.raw              || '-';
  let forwardEps              = object.quoteSummary.result[0]?.defaultKeyStatistics?.forwardEps?.raw               || '-';
  let pegRatio                = object.quoteSummary.result[0]?.defaultKeyStatistics?.pegRatio?.raw                 || '-';
  let priceToBook             = object.quoteSummary.result[0]?.defaultKeyStatistics?.priceToBook?.raw              || '-';
  let returnOnAssets          = object.quoteSummary.result[0]?.financialData?.returnOnAssets?.raw                  || '-';
  let returnOnEquity          = object.quoteSummary.result[0]?.financialData?.returnOnEquity?.raw                  || '-';

  let enterpriseValue         = object.quoteSummary.result[0]?.defaultKeyStatistics?.enterpriseValue?.raw          || '-';
  let bookValue               = object.quoteSummary.result[0]?.defaultKeyStatistics?.bookValue?.raw                || '-';

  return [
    marketCap, dividendRate, dividendYield, payoutRatio, fiveYAvgDivYield, insidersPercentHeld, institutionsPercentHeld,
    earningsDate, exDividendDate, dividendDate,
    totalRevenue, revenueGrowth, revenuePerShare, ebitda, grossProfits, earningsGrowth, grossMargins, ebitdaMargins, operatingMargins, profitMargins,
    totalCash, freeCashflow, opCashflow, cashPerShare,
    totalDebt, debtToEquity,
    quickRatio, currentRatio, trailingEps, forwardEps, pegRatio, priceToBook, returnOnAssets, returnOnEquity,
    enterpriseValue, bookValue
  ];
}

CodePudding user response:

Here (line 10 as said in the error):

const [ticker, b, c, d, e, f, g, h, i, j, k, l, m, n, r, o, p, q, r, s, t, u, v, w, x, y, z, aa, ab, ac, ad, ae, af, ag, ah, ai, aj, ak, al, am, an, ao] = zr;

you have two r, one after the n and the other at its correct position

Edit: Take care that in the zo.values.push you are also pushing it two times, probably because it was copy/pasted

  • Related