Goal:
Import data from coinGecko API server to Google Apps Script then send it to Google Sheets.
It imports yesterday's information from the coinGecko server.
Issue:
The API server returns an error, only at the beginning (end) of the month.
Error message:
Jul 1, 2022, 2:40:43 AM Error Exception:
Request failed for https://api.coingecko.com returned code 400. Truncated server response: {"error":"invalid date"} (use muteHttpExceptions option to examine full response) at fetchJsonObject(Code:62:36)
at updateSheet(Code:45:34) at tryCatch(Code:38:12) at tryCatch(Code:41:12) at tryCatch(Code:41:12) at tryCatch(Code:41:12) at updateSheets(Code:4:17) at getImportData0(Code:7:3)
Hypothesis:
It should be the problem of the date object. I am using Javascript default date object. I converted it to DDMMYYYY format. But that is pretty much all I did for this date code block. I assume the root cause of this issue should be around this area, but it seems working correctly.
Should I use another date object?
Source code:
function getImportData0() {
function updateSheets() {
for (let i = 0; i < CoinGecko.coinSheetNames.length; i++) {
CoinGecko.tryCatch(i);
}
}
updateSheets();
}
const CoinGecko = {
// # Entity
serverName: 'https://api.coingecko.com/api/v3/',
endpointName: 'coins/',
coinSheetNames: [
{ bitcoin: 'Bitcoin (BTC)' },
{ aave: 'Aave (AAVE)' },
{ aeon: 'AEON (AEON)' },
{ cardano: 'Cardano (ADA)' },
{ 'pirate-chain': 'Pirate Chain (ARRR)' },
{ dero: 'Dero (DERO)' },
{ enjincoin: 'Enjin (ENJ)' },
{ eos: 'EOS (EOS)' },
{ 'loki-network': 'Oxen (OXEN)' },
{ wownero: 'WowNero (WOW)' },
{ triton: 'Equilibria (XEQ)' },
{ haven: 'Haven (XHV)' },
{ monero: 'Monero (XMR)' },
{ ripple: 'Ripple (XRP)' },
],
resourceType: 'history',
subResourceType: ['market_data'],
fieldTypes: ['market_cap', 'total_volume', 'current_price'],
currency: 'usd',
// # Main Functions
tryCatch: function (i) {
try {
this.updateSheet(i);
} catch (error) {
CoinGecko.handleError(error);
this.tryCatch(i);
}
},
updateSheet: function (i) {
const jsonObject = CoinGecko.fetchJsonObject(i);
const record = CoinGecko.getRecord(jsonObject);
CoinGecko.setRecordToSheet(record, i);
},
handleError: function (error) {
if (!error.toString().includes('1015')) {
throw error;
}
console.log(
`CoinGecko server is busy. Restarting the program. [Errror Message]: ${error}`
);
Utilities.sleep(1000);
},
// # Input Boundary
fetchJsonObject: function (i) {
const requestUrl = this.getRequestUrl(i);
const jsonString = UrlFetchApp.fetch(requestUrl).getContentText();
const jsonObject = JSON.parse(jsonString);
return jsonObject;
},
getRequestUrl: function (i) {
const coinName = Object.keys(this.coinSheetNames[i]);
return (
this.serverName +
this.endpointName +
coinName +
'/' +
this.resourceType +
'?date=' +
this.getddmmyyyyYesterday()
);
},
// #Interactor
getRecord: function (jsonObject) {
let record = [];
this.pushDate(record);
this.pushOtherFields(record, jsonObject);
return record;
},
pushDate: function (record) {
const date = this.getyyyymmddYesterday();
record.push(date);
},
pushOtherFields: function (record, jsonObject) {
const fieldTypes = this.fieldTypes;
for (let i = 0; i < fieldTypes.length; i++) {
this.pushOtherField(record, jsonObject, fieldTypes[i]);
}
},
pushOtherField: function (record, jsonObject, fieldType) {
marketData = jsonObject[this.subResourceType[0]];
currency = this.currency;
const value = marketData[fieldType][currency];
record.push(value);
return record;
},
getddmmyyyyYesterday() {
return this.getYesterday('day', 'month', 'year');
},
getyyyymmddYesterday: function () {
return this.getYesterday('year', 'month', 'day');
},
getYesterday: function (top, middle, end) {
return (date =
this.getYearMonthDay()[top] +
'-' +
this.getYearMonthDay()[middle] +
'-' +
this.getYearMonthDay()[end]);
},
getYearMonthDay: function () {
const now = new Date();
const yearMonthDay = {};
yearMonthDay['year'] = now.getFullYear();
yearMonthDay['month'] = this.getMonth(now);
yearMonthDay['day'] = ('0' + (now.getDate() - 1)).slice(-2);
return yearMonthDay;
},
getMonth: function (now) {
const month = ( '0' + (now.getMonth() + 1))//.slice(-2)
// ('0' + (now.getMonth() + 1)).slice(-2);
return month;
},
// # Output Boundary
setRecordToSheet: function (record, i) {
const sheetName = Object.values(this.coinSheetNames[i]); /* [0] */
const range = this.getRange(record, i, sheetName);
range.setValues([record]);
console.log(`Importing ${sheetName} Completed`);
},
getSheet: function (sheetName) {
const ss = SpreadsheetApp.getActive();
const sheet = ss.getSheetByName(sheetName);
if (sheet == null) {
throw new Error(
`'${sheetName}' sheet is not found in this Spreadsheet. Please fix the sheet name or create a new sheet.`
);
}
return sheet;
},
getRange: function (record, i, sheetName) {
const sheet = this.getSheet(sheetName);
let lastRow = sheet.getLastRow();
if (lastRow == null) {
lastRow = 1;
}
const colRow = record.length;
const range = sheet.getRange(lastRow + 1, 1, 1, colRow);
return range;
},
};
Short answer: Change your getYearMonthDay() function to this:
getYearMonthDay: function () {
var now = new Date();
now.setDate(now.getDate()-1)
const yearMonthDay = {};
yearMonthDay['year'] = now.getFullYear();
yearMonthDay['month'] = this.getMonth(now);
yearMonthDay['day'] = ('0' + now.getDate()).slice(-2);
return yearMonthDay;
}
Long answer: There's a bug in your code when calculating yesterday's date. You're just subtracting 1 from now.getDate(), which just returns an integer, when you should instead subtract 1 from the now object, which is a Date.
Here's a way to check it out by yourself. I added a console.log(yearMonthDay) to see what it generates, and I set the Date object to the beginning of the month using const now = new Date("July 1, 2022");
function getYearMonthDay() {
const now = new Date("July 1, 2022");
const yearMonthDay = {};
yearMonthDay['year'] = now.getFullYear();
yearMonthDay['month'] = this.getMonth(now);
yearMonthDay['day'] = ('0' + (now.getDate() - 1)).slice(-2);
console.log(yearMonthDay)
return yearMonthDay;
}
The output is this:
{ year: 2022, month: '07', day: '00' }
A day 00 is certainly an invalid date. You can just use setDate() on the original Date object and subtract 1 (change it to a variable or create a new one if you want to keep it as constant).
function getYearMonthDay() {
var now = new Date("July 1, 2022");
now.setDate(now.getDate()-1) //turns into yesterday
const yearMonthDay = {};
yearMonthDay['year'] = now.getFullYear();
yearMonthDay['month'] = this.getMonth(now);
yearMonthDay['day'] = ('0' + now.getDate()).slice(-2);
console.log(yearMonthDay)
return yearMonthDay;
}
Here the output is as it should be:
{ year: 2022, month: '06', day: '30' }