如何使用API在Google表格中检索Google财经历史数据? [英] How do I retrieve Google Finance Historical data in Google Sheets using the API?
问题描述
要重现此问题，创建一个新的工作表. 在单元格A1中，插入以下公式:
To reproduce this problem, create a new sheet. In cell A1, insert this formula:
=GoogleFinance("AMZN", "all", "1/1/2018", "2/1/2018")
输出(格式化与该问题无关)
The output (Formatting doesn't matter for this problem):
Date Open High Low Close Volume
1/2/2018 16:00:00 1172 1190 1170.51 1189.01 2694494
1/3/2018 16:00:00 1188.3 1205.49 1188.3 1204.2 3108793
1/4/2018 16:00:00 1205 1215.8699 1204.66 1209.59 3022089
1/5/2018 16:00:00 1217.51 1229.14 1210 1229.14 3544743
1/8/2018 16:00:00 1236 1253.079 1232.03 1246.87 4279475
1/9/2018 16:00:00 1256.9 1259.33 1241.76 1252.7 3661316
1/10/2018 16:00:00 1245.15 1254.33 1237.23 1254.33 2686017
1/11/2018 16:00:00 1259.74 1276.77 1256.46 1276.68 3125048
1/12/2018 16:00:00 1273.3925 1305.76 1273.3925 1305.2 5443730
1/16/2018 16:00:00 1323 1339.94 1292.3 1304.86 7220701
1/17/2018 16:00:00 1312.24 1314 1280.88 1295 5253754
1/18/2018 16:00:00 1293.95 1304.6 1284.02 1293.32 4026915
1/19/2018 16:00:00 1312 1313 1292.99 1294.58 4578536
1/22/2018 16:00:00 1297.17 1327.45 1296.6636 1327.31 4140061
1/23/2018 16:00:00 1338.09 1364.9 1337.34 1362.54 5169306
1/24/2018 16:00:00 1374.82 1388.16 1338 1357.51 6807457
1/25/2018 16:00:00 1368 1378.34 1357.62 1377.95 4753012
1/26/2018 16:00:00 1392.01 1402.53 1380.91 1402.05 4857310
1/29/2018 16:00:00 1409.18 1431.39 1400.44 1417.68 5701898
1/30/2018 16:00:00 1403.17 1439.25 1392 1437.82 5871942
1/31/2018 16:00:00 1451.3 1472.58 1450.04 1450.89 6424693
到目前为止一切都很好.但是，当我尝试通过API检索此数据时(我想指出的是，所有其他数据都可以很好地检索到)，对于单元格A1，我得到一个简单的#N/A"，而对于其他所有单元格则没有任何值细胞.就像他们是空的.我试图只检索值，但也检索所有的gridinfo，这两种情况都好像这些值不存在.
All is well so far. BUT, when I try to retrieve this data via the API (And I want to point out that all OTHER data is retrieved just fine), I get simple a "#N/A" for cell A1, and no values for all the other cells. It's like they're empty. I've tried retrieving just the values, but also all the gridinfo, both cases, it's like these values were never there.
我尝试过的事情:
- 在读取之前将单元格设置为"= TODAY()"(有些评论说会触发数据刷新).
- 尝试读取数据时会出现延迟，但这会永远持续下去.
- 所有这些都可以在浏览器中完美运行，但不能通过API调用
- 引用此数据的其他单元格失败，并显示#N/A"
- 如果我添加了附加的CryptoFinance，并插入了此函数"= CRYPTOFINANCE(" COINMARKETCAP)"，则会填充一系列单元格，类似于GoogleFinance.但是，可以通过API调用来检索这些单元格.就像魅力一样.
-
使用googlefinance的任何单个"单元格都可以正常工作，因此，如果我添加以下所示的单元格I1，则可以很好地检索到该值
- Setting a cell to "=TODAY()" before reading (some comments say that triggers a refresh of the data).
- Looping with a delay trying to read the data, but that runs forever.
- All this works flawlessly in the browser, but not over API calls
- Other cells referencing this data fails with "#N/A"
- If I add the add-on CryptoFinance, and insert this function "=CRYPTOFINANCE("COINMARKETCAP")", a range of cells are populated, similar to GoogleFinance. However, these cells CAN be retrieved via API calls. Works like a charm.
Any 'single' cell using googlefinance works fine, so if I add cell I1 shown below, THAT value is retrieved just fine
=GoogleFinance("AMZN", "price")
当使用来自Google财务的历史数据(根据API调用)时，数据似乎根本就不存在.
It just simply seems like the data is not there when using the HISTORICAL data from google finance (as per the API call).
我尚未发布我的代码，因为看来这更多是googlefinance无法正常工作的情况，尤其是在返回数据的范围"而不是单个单元格值的情况下.
I have not posted my code, since it seems this is more a case of googlefinance not working than anything else, and specifically when returning a 'range' of data instead of single cell values.
有想法吗?
推荐答案
Duh.
藏在 Google财务文档 n的最底部我终于注意到了这个小宝石:
Tucked at the very bottom of the Google Finance Documentation I finally noticed this little gem:
历史数据无法通过Sheets API或Apps脚本下载或访问.如果您尝试这样做，则会在电子表格的相应单元格中看到一个#N/A错误代替值.
Historical data cannot be downloaded or accessed via the Sheets API or Apps Script. If you attempt to do so, you will see a #N/A error in place of the values in the corresponding cells of your spreadsheet.
这篇关于如何使用API在Google表格中检索Google财经历史数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
