(DataFlex) Google Sheets - Append Values to an Existing Spreadsheet

Appends values to an existing Google spreadsheet.

Note: This example requires Chilkat v11.0.0 or greater.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

Use ChilkatAx-win32.pkg

Procedure Test
    Boolean iSuccess
    Variant vJson
Token    Handle hoJsonToken
    Handle hoHttp
    Variant vJson
    Handle hoJson
    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

    // To append values to an existing spreadsheet, our HTTP request body will
    // contain JSON in the format of a "ValueRange".  For example, the spreadsheet we'll be
    // adding to in this example looks like this:

    // 

    // The JSON ValueRange for the cells in the above spreadsheet is:
    // {
    //   "range": "Sheet1!A1:B5",
    //   "majorDimension": "ROWS",
    //   "values": [
    //     [
    //       "Item",
    //       "Cost"
    //     ],
    //     [
    //       "Wheel",
    //       "$20.50"
    //     ],
    //     [
    //       "Door",
    //       "$15"
    //     ],
    //     [
    //       "Engine",
    //       "$100"
    //     ],
    //     [
    //       "Totals",
    //       "$135.50"
    //     ]
    //   ]
    // }

    // This example will append 6 cells (3 rows / 2 columns).
    // We'll be appending the following:
    // 
    // "Paint", "$100"
    // "Brakes", "$100"
    // "New Total", "$335.50"
    // 

    // The range of cells we'll be appending is A1:B5
    // Therefore, the ValueRange JSON we'll be sending in our POST body is:

    // {
    //   "range": "Sheet1!A1:B5",
    //   "majorDimension": "ROWS",
    //   "values": [
    //     [
    //       "Paint",
    //       "$100"
    //     ],
    //     [
    //       "Brakes",
    //       "$100"
    //     ],
    //     [
    //       "New Total",
    //       "$335.50"
    //     ]
    //   ]
    // }

    Get Create (RefClass(cComChilkatJsonObject)) To hoJson
    If (Not(IsComObjectCreated(hoJson))) Begin
        Send CreateComObject of hoJson
    End
    Get ComUpdateString Of hoJson "range" "Sheet1!A1:B5" To iSuccess
    Get ComUpdateString Of hoJson "majorDimension" "ROWS" To iSuccess

    Set ComI Of hoJson To 0
    Set ComJ Of hoJson To 1
    Get ComUpdateString Of hoJson "values[i][j]" "Paint" To iSuccess
    Set ComJ Of hoJson To 1
    Get ComUpdateString Of hoJson "values[i][j]" "$100" To iSuccess

    Set ComI Of hoJson To 1
    Set ComJ Of hoJson To 0
    Get ComUpdateString Of hoJson "values[i][j]" "Brakes" To iSuccess
    Set ComJ Of hoJson To 1
    Get ComUpdateString Of hoJson "values[i][j]" "$100" To iSuccess

    Set ComI Of hoJson To 2
    Set ComJ Of hoJson To 0
    Get ComUpdateString Of hoJson "values[i][j]" "Totals" To iSuccess
    Set ComJ Of hoJson To 1
    Get ComUpdateString Of hoJson "values[i][j]" "$335.50" To iSuccess

    Set ComEmitCompact Of hoJson To False
    Get ComEmit Of hoJson To sTemp1
    Showln sTemp1

    // Send the POST to:
    // https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}:append?valueInputOption=USER_ENTERED

    Get ComSetUrlVar Of hoHttp "spreadsheetId" "1_SO2L-Y6nCayNpNppJLF0r9yHB2UnaCleGCKeE4O0SA" To iSuccess
    Get ComSetUrlVar Of hoHttp "range" "Sheet1!A1:B5" To iSuccess
    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 "POST" "https://sheets.googleapis.com/v4/spreadsheets/{$spreadsheetId}/values/{$range}:append?valueInputOption=USER_ENTERED" vJson "application/json" vResp To iSuccess
    If (iSuccess = False) Begin
        Get ComLastErrorText Of hoHttp To sTemp1
        Showln sTemp1
        Procedure_Return
    End

    Get ComStatusCode Of hoResp To iTemp1
    Showln "response status code = " iTemp1
    Get ComBodyStr Of hoResp To sTemp1
    Showln "response JSON = " sTemp1

    // Sample output:
    // 
    // response status code = 200
    // response JSON = {
    //   "spreadsheetId": "1_SO2L-Y6nCayNpNppJLF0r9yHB2UnaCleGCKeE4O0SA",
    //   "tableRange": "Sheet1!A1:B5",
    //   "updates": {
    //     "spreadsheetId": "1_SO2L-Y6nCayNpNppJLF0r9yHB2UnaCleGCKeE4O0SA",
    //     "updatedRange": "Sheet1!A6:B8",
    //     "updatedRows": 3,
    //     "updatedColumns": 2,
    //     "updatedCells": 6
    //   }
    // }
    // 

    // Our Google Spreadsheet now looks like this:
    // 


End_Procedure