Integrate API in a spreadsheet
Google Drive
1. Modify regional configuration
API generates CSV files using “.” as a decimal character. You should choose "United States" regional configuration so Google Spreadsheet reads properly the file, or any other compatible region.
Alternatively, you can ask the API to change the decimal character using decimal parameter.
2. Import data to the spreadsheet
IMPORTDATA() function takes the API call URL and gets data to the spreadsheet.
3. Choose date format
Time index can be seen as a number first time data is imported. You can select the entire column and choose the format to visualize date values.
4. Modify API call URL
Once you have imported a table for the first time, you can modify parameters to change the query. The table will be updated each time.
Excel
1. Generate a query from URL
“Data" > "New query" > “From other sources" > “From web”
2. Edit original file codification
API generates CSV files using "Unicode UTF-8" encoding, which is not default in Excel. Click in "Origin" > "File origin" > Choose "Unicode UTF-8".
3. Edit column types
Excel can read wrong column types if the decimal separator is not ".". You should use advanced editor to correct column data types. (You can also use decimal parameter to change the decimal separator)
- “indice_tiempo” column data type should be “type date”
- Rest of the columns, (containing series) should be “type number”
4. Modify regional configuration
In the same advanced editor, regional configurations should be changed at the end to be “en-US” and accepts “.” as a decimal separator (unless decimal parameter is used).
5. Save modifications and load the query
For last, you can click in “Close and load" and the query will be configured in an Excel table that can be updated.
