(PureBasic) Google Sheets - Read a Single Range

Reads the values stored in the range Sheet1!A1:B5 and returns them in the response.

Chilkat PureBasic Module Download Chilkat PureBasic Module

IncludeFile "CkHttp.pb"
IncludeFile "CkJsonObject.pb"

Procedure ChilkatExample()

    ;  This example requires the Chilkat API to have been previously unlocked.
    ;  See Global Unlock Sample for sample code.

    ;  This example uses a previously obtained access token having permission for the
    ;  Google Sheets scope.

    ;  In this example, Get Google Sheets OAuth2 Access Token, the access
    ;  token was saved to a JSON file.  This example fetches the access token from the file..
    jsonToken.i = CkJsonObject::ckCreate()
    If jsonToken.i = 0
        Debug "Failed to create object."
        ProcedureReturn
    EndIf

    success.i = CkJsonObject::ckLoadFile(jsonToken,"qa_data/tokens/googleSheets.json")
    If CkJsonObject::ckHasMember(jsonToken,"access_token") = 0
        Debug "No access token found."
        CkJsonObject::ckDispose(jsonToken)
        ProcedureReturn
    EndIf

    http.i = CkHttp::ckCreate()
    If http.i = 0
        Debug "Failed to create object."
        ProcedureReturn
    EndIf

    CkHttp::setCkAuthToken(http, CkJsonObject::ckStringOf(jsonToken,"access_token"))

    ;  Get the cells defined by the range A1:B5
    jsonResponse.s = CkHttp::ckQuickGetStr(http,"https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId/values/Sheet1!A1:B5")
    If CkHttp::ckLastMethodSuccess(http) <> 1
        Debug CkHttp::ckLastErrorText(http)
        CkJsonObject::ckDispose(jsonToken)
        CkHttp::ckDispose(http)
        ProcedureReturn
    EndIf

    Debug jsonResponse

    json.i = CkJsonObject::ckCreate()
    If json.i = 0
        Debug "Failed to create object."
        ProcedureReturn
    EndIf

    CkJsonObject::ckLoad(json,jsonResponse)

    ;  A sample response is shown below.
    ;  To generate the parsing source code for a JSON response, paste
    ;  the JSON into this online tool: Generate JSON parsing code

    ;  {
    ;    "range": "Sheet1!A1:B5",
    ;    "majorDimension": "ROWS",
    ;    "values": [
    ;      [
    ;        "Item",
    ;        "Cost"
    ;      ],
    ;      [
    ;        "Wheel",
    ;        "$20.50"
    ;      ],
    ;      [
    ;        "Door",
    ;        "$15"
    ;      ],
    ;      [
    ;        "Engine",
    ;        "$100"
    ;      ],
    ;      [
    ;        "Totals",
    ;        "$135.50"
    ;      ]
    ;    ]
    ;  }

    i.i
    count_i.i
    j.i
    count_j.i

    range.s = CkJsonObject::ckStringOf(json,"range")
    majorDimension.s = CkJsonObject::ckStringOf(json,"majorDimension")
    i = 0
    count_i = CkJsonObject::ckSizeOfArray(json,"values")
    While i < count_i
        CkJsonObject::setCkI(json, i)
        j = 0
        count_j = CkJsonObject::ckSizeOfArray(json,"values[i]")
        While j < count_j
            CkJsonObject::setCkJ(json, j)
            strVal.s = CkJsonObject::ckStringOf(json,"values[i][j]")
            j = j + 1
        Wend
        i = i + 1
    Wend


    CkJsonObject::ckDispose(jsonToken)
    CkHttp::ckDispose(http)
    CkJsonObject::ckDispose(json)


    ProcedureReturn
EndProcedure