search

thisdavej / getJSON-google-sheets

Functions to fetch, filter, and sort JSON Web APIs in Google Sheets and render the results using a simple and powerful string template.
MIT License
38 stars 9 forks source link

How to deal with point in the template #8

Closed cristipurdel closed 4 years ago

cristipurdel commented 4 years ago

I am trying to read all of “1. open” but is not working I think due to the point in the way. https://www.alphavantage.co/query?function=TIME_SERIES_MONTHLY_ADJUSTED&symbol=MSFT&apikey=demo Any chance to bypass this?

thisdavej commented 4 years ago

Great question! Given the following excerpt of JSON returned from the URL...

{
    "Meta Data": {
        "1. Information": "Monthly Adjusted Prices and Volumes",
        "2. Symbol": "MSFT",
        "3. Last Refreshed": "2020-03-19 10:04:59",
        "4. Time Zone": "US/Eastern"
    },
    "Monthly Adjusted Time Series": {
        "2020-03-19": {
            "1. open": "165.3100",
            "2. high": "175.0000",
            "3. low": "135.0000",
            "4. close": "144.1300",
            "5. adjusted close": "144.1300",
            "6. volume": "948571908",
            "7. dividend amount": "0.0000"
        },
        "2020-02-28": {
            "1. open": "170.4300",
            "2. high": "190.7000",
            "3. low": "152.0000",
            "4. close": "162.0100",
            "5. adjusted close": "162.0100",
            "6. volume": "887894931",
            "7. dividend amount": "0.5100"
        }
    }
}

...you can use the following template in the JSON function:

{{"Monthly Adjusted Time Series".2020-03-19."1. open"}}

Note the use double quotes to handle the spaces in the "Monthly Adjusted Time Series" field and the use of double quotes to also handle "1. open" since it contains both a space and a "." which both need to be shielded from interpretation.

Since the JSON object returned from the API does not utilize an array, you will need to create multiple rows manually and enter "2020-03-19", for example, in one cell to render a template in a different cell containing the date for that row. You can use the Sheets NOW() function to render today's date and calculate from there to show the last N days in other rows, but each row will need to use a separate GETJSON() function and thus fetch the same URL multiple times since the JSON API does not return an array of values. This is not a huge problem as it will work fine (and we can't control the JSON API returned in this case), but it is a bit less than optimal.