PureBasic

Google Sheets - Read a Single Range

See more Google Sheets Examples

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

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

Procedure ChilkatExample()

    success.i = 0

    ; 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 = 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"))
    CkHttp::setCkSessionLogFilename(http, "qa_output/sessionLog.txt")

    ; 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