google sheets formatting pasting array -
i have script gets information api , writes google sheets. in data api numbers normal once written google sheets changed. example 4319.05 written 4319:05:00.000. when try format range after been pasted sheet strange results 4319:05:00.000 formatted number, gives value of 179.96.
the api can found at: https://api.coinmarketcap.com/v1/ticker/
the code used follows:
function getcoinmarketcapapi() { var ss = spreadsheetapp.getactivespreadsheet(); var sheets = ss.getsheets(); var sheet = ss.getactivesheet(); logger.log(sheets) var url="https://api.coinmarketcap.com/v1/ticker/"; var response = urlfetchapp.fetch(url); // feed var dataall = json.parse(response.getcontenttext()); // var dataset = dataall; logger.log(dataset) var rows = [], data; logger.log(rows) (i = 0; < dataset.length; i++) { data = dataset[i]; rows.push([data.id,data.name,data.symbol,data.rank,data.price_usd,data.price_btc,data.percent_change_1h,data.percent_change_24h,data.percent_change_7d,data.last_updated]); } datarange = sheet.getrange(6, 1, rows.length, 10); datarange.setvalues(rows); }
any appreciated! =)
thanks in advance!
your script worked fine numbers. did have fix date. format sheet automatic.
function getcoinmarketcapapi() { var ss = spreadsheetapp.getactivespreadsheet(); var sheets = ss.getsheets(); var sheet = ss.getactivesheet(); var url="https://api.coinmarketcap.com/v1/ticker/"; var response = urlfetchapp.fetch(url); // feed var dataall = json.parse(response.getcontenttext()); // var dataset = dataall; var rows = [],data; (i = 0; < dataset.length; i++) { data = dataset[i]; var d=new date(data.last_updated*1000)// convert date rows.push([data.id,data.name,data.symbol,data.rank,data.price_usd,data.price_btc,data.percent_change_1h,data.percent_change_24h,data.percent_change_7d,d]); } datarange = sheet.getrange(6, 1, rows.length, 10); datarange.setvalues(rows); }
here test spreadsheet can copy , try.
https://docs.google.com/spreadsheets/d/1dg2opjg3nec_ud5mrxnufpldxnvoo9_ul-qw_mczwz8/edit?usp=sharing
Comments
Post a Comment