Im encountering an issue with fetching data from the Alphavantage API using Google Apps Script. I have a script set up to fetch data for four specific functions ("OVERVIEW", "BALANCE_SHEET", "CASH_FLOW", "INCOME_STATEMENT"), with the ticker symbol dynamically retrieved from cell I6 in my Google Sheets.
However, when I run the script, it fetches data successfully for the ticker symbol, for the first 4 cycles it fetches data succesfuly (as it should) but in fifth, additional unwanted cycle the function name passed to the API changes to undefined, resulting in a URL like: https://www.alphavantage.co/query?function=undefined&symbol=aapl&apikey=9R6A1KGBO4C1CYWD.
I've checked my code thoroughly and ensured that the function names are correctly passed to the getStock function, yet the issue persists. Any insights or suggestions on resolving this would be greatly appreciated!
const apiKey = "XXXXXX";
const alphaVantageBaseUrl = "https://www.alphavantage.co/query";
async function fetchData(functionUrl) {
try {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheetWithTicker = spreadsheet.getSheets()[2];
const tickerSymbol = sheetWithTicker.getRange("I6").getValue();
const url = `${functionUrl}&symbol=${tickerSymbol}&apikey=${apiKey}`;
console.log("URL to fetch: " + url);
const response = await UrlFetchApp.fetch(url);
if (response.getResponseCode() === 200) {
return JSON.parse(response.getContentText());
} else {
throw new Error(`Unable to fetch data. Err: ${response.getResponseCode()}`);
}
} catch (error) {
throw new Error(`Error occurred during fetch: ${error}`);
}
}
async function fetchAllData() {
try {
const functionUrls = [
`${alphaVantageBaseUrl}?function=OVERVIEW`,
`${alphaVantageBaseUrl}?function=BALANCE_SHEET`,
`${alphaVantageBaseUrl}?function=INCOME_STATEMENT`,
`${alphaVantageBaseUrl}?function=CASH_FLOW`
];
const fetchedData = await Promise.all(functionUrls.map(url => fetchData(url)));
console.log("Fetched data:", fetchedData);
} catch (error) {
console.error(`Error occurred: ${error}`);
}
}
fetchAllData();
console logs:
URL to fetch: https://www.alphavantage.co/query?function=OVERVIEW&symbol=aapl&apikey=XXXXXX
URL to fetch: https://www.alphavantage.co/query?function=BALANCE_SHEET&symbol=aapl&apikey=XXXXXX
URL to fetch: https://www.alphavantage.co/query?function=INCOME_STATEMENT&symbol=aapl&apikey=XXXXXX
URL to fetch: https://www.alphavantage.co/query?function=CASH_FLOW&symbol=aapl&apikey=XXXXXX
URL to fetch: undefined&symbol=aapl&apikey=XXXXXX
Error Error: Error occurred during fetch: Exception: Bad request: http://undefined&symbol=aapl&apikey=XXXXXX
fetchData @ Code.gs:19