CODEWITHSUNDEEP
excelconnectiongoogle-drive-apigoogle-docs
Prometheus
Prometheus

Reputation: 153

Excel Connections in Google Drive

I have an Excel 2010 file (.xlsx) with multiple connections to import data from the web. Instead of having to open the file manually and click "Refresh All", I want to use Google Docs (specifically Google Sheets) to automate the refresh process every 60 minutes. I uploaded the xlsx file to my Google Drive but the connections do not work anymore. How do I get around this?

Additional information: 1. The data connections are to Kayak flight search service. Here's an example page. I am importing the table data at the top of the page which shows prices for flexible dates. 2. I tried using IMPORTHTML in Google Sheets but for some reason it doesn't identify the above table as an HTML table. I get a parse error.

Upvotes: 2

Views: 879

Answers (1)

mb21
mb21

Reputation: 39237

I used Kimono to generate this API (you have to sign up to use it) which can return CSV of the form:

"collection1"
"depart-sat.text","depart-sat.href"
"$798","javascript: FilterList.flexFilterToDates('20141213', '20150111', 798)"
"$798","javascript: FilterList.flexFilterToDates('20141213', '20150112', 798)"
...

"collection2"
"depart-sun.text","depart-sun.href"
"$1127","javascript: FilterList.flexFilterToDates('20141214', '20150111', 1127)"
"$1211","javascript: FilterList.flexFilterToDates('20141214', '20150112', 1211)"
...

In Google Spreadsheet you can get the data with =ImportDATA("https://www.kimonolabs.com/api/csv/ccyfjzlm?apikey=YOURAPIKEY").

Upvotes: 1

Related Questions