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
BandCare 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 columnsDtoAO
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
];
}
>Solution :
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