DataFlex

Google Sheets - Update (Set Values in a Range)

See more Google Sheets Examples

Sets values in a range of a spreadsheet. This example will demonstrate by first getting a range, then changing some values in the JSON, and then HTTPS PUT the changes back to the Google Sheet. DataFlex

Use ChilkatAx-win32.pkg

Procedure Test
    Boolean iSuccess
    Variant vJson
Token    Handle hoJsonToken
    Handle hoHttp
    String sJsonResponse
    Variant vJson
    Handle hoJson
    String sUrlToUpdate
    String sXyz
    Variant vResp
    Handle hoResp
    String sTemp1
    Integer iTemp1
    Boolean bTemp1

    Move False To iSuccess

    // 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..
    Get Create (RefClass(cComChilkatJsonObject)) To hoJsonToken
    If (Not(IsComObjectCreated(hoJsonToken))) Begin
        Send CreateComObject of hoJsonToken
    End
    Get ComLoadFile Of hoJsonToken "qa_data/tokens/googleSheets.json" To iSuccess
    Get ComHasMember Of hoJsonToken "access_token" To bTemp1
    If (bTemp1 = False) Begin
        Showln "No access token found."
        Procedure_Return
    End

    Get Create (RefClass(cComChilkatHttp)) To hoHttp
    If (Not(IsComObjectCreated(hoHttp))) Begin
        Send CreateComObject of hoHttp
    End
    Get ComStringOf Of hoJsonToken "access_token" To sTemp1
    Set ComAuthToken Of hoHttp To sTemp1

    // First get the cells in the range A1:B5
    Get ComSetUrlVar Of hoHttp "range" "Sheet1!A1:B5" To iSuccess
    Get ComSetUrlVar Of hoHttp "spreadsheetId" "1_SO2L-Y6nCayNpNppJLF0r9yHB2UnaCleGCKeE4O0SA" To iSuccess
    Get ComQuickGetStr Of hoHttp "https://sheets.googleapis.com/v4/spreadsheets/{$spreadsheetId}/values/{$range}" To sJsonResponse
    Get ComLastMethodSuccess Of hoHttp To bTemp1
    If (bTemp1 = False) Begin
        Get ComLastErrorText Of hoHttp To sTemp1
        Showln sTemp1
        Procedure_Return
    End

    Showln sJsonResponse

    Get Create (RefClass(cComChilkatJsonObject)) To hoJson
    If (Not(IsComObjectCreated(hoJson))) Begin
        Send CreateComObject of hoJson
    End
    Set ComEmitCompact Of hoJson To False
    Get ComLoad Of hoJson sJsonResponse To iSuccess

    // A sample response is shown below.

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

    // We're going to change the cost of the Engine to $120, and the Totals to $155.50

    Set ComI Of hoJson To 3
    Set ComJ Of hoJson To 1
    Get ComUpdateString Of hoJson "values[i][j]" "$120" To iSuccess
    Set ComI Of hoJson To 4
    Get ComUpdateString Of hoJson "values[i][j]" "$155.50" To iSuccess

    // Show the updated JSON.
    Get ComEmit Of hoJson To sTemp1
    Showln sTemp1

    // Update the Google Sheet using a PUT request.
    Set ComEmitCompact Of hoJson To True
    Move "https://sheets.googleapis.com/v4/spreadsheets/{$spreadsheetId}/values/{$range}?valueInputOption=USER_ENTERED" To sUrlToUpdate
    Get ComQuickGetStr Of hoHttp sUrlToUpdate To sXyz
    Get Create (RefClass(cComChilkatHttpResponse)) To hoResp
    If (Not(IsComObjectCreated(hoResp))) Begin
        Send CreateComObject of hoResp
    End
    Get pvComObject of hoJson to vJson
    Get pvComObject of hoResp to vResp
    Get ComHttpJson Of hoHttp "PUT" sUrlToUpdate vJson "application/json" vResp To iSuccess
    If (iSuccess = False) Begin
        Get ComLastErrorText Of hoHttp To sTemp1
        Showln sTemp1
        Procedure_Return
    End

    // Examine the response..
    Get ComStatusCode Of hoResp To iTemp1
    Showln "response status code = " iTemp1
    Showln "response body:"
    Get ComBodyStr Of hoResp To sTemp1
    Showln sTemp1

    // A sample response body:

    // {
    //   "spreadsheetId": "1_SO2L-Y6nCayNpNppJLF0r9yHB2UnaCleGCKeE4O0SA",
    //   "updatedRange": "Sheet1!A1:B5",
    //   "updatedRows": 5,
    //   "updatedColumns": 2,
    //   "updatedCells": 10
    // }


End_Procedure