|
|
(Classic ASP) Google Sheets - Append Values to an Existing Spreadsheet
Appends values to an existing Google spreadsheet.
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body>
<%
' 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..
' For versions of Chilkat < 10.0.0, use CreateObject("Chilkat_9_5_0.JsonObject")
set jsonToken = Server.CreateObject("Chilkat.JsonObject")
success = jsonToken.LoadFile("qa_data/tokens/googleSheets.json")
If (jsonToken.HasMember("access_token") = 0) Then
Response.Write "<pre>" & Server.HTMLEncode( "No access token found.") & "</pre>"
Response.End
End If
' For versions of Chilkat < 10.0.0, use CreateObject("Chilkat_9_5_0.Http")
set http = Server.CreateObject("Chilkat.Http")
http.AuthToken = jsonToken.StringOf("access_token")
' 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"
' ]
' ]
' }
' For versions of Chilkat < 10.0.0, use CreateObject("Chilkat_9_5_0.JsonObject")
set json = Server.CreateObject("Chilkat.JsonObject")
success = json.UpdateString("range","Sheet1!A1:B5")
success = json.UpdateString("majorDimension","ROWS")
json.I = 0
json.J = 1
success = json.UpdateString("values[i][j]","Paint")
json.J = 1
success = json.UpdateString("values[i][j]","$100")
json.I = 1
json.J = 0
success = json.UpdateString("values[i][j]","Brakes")
json.J = 1
success = json.UpdateString("values[i][j]","$100")
json.I = 2
json.J = 0
success = json.UpdateString("values[i][j]","Totals")
json.J = 1
success = json.UpdateString("values[i][j]","$335.50")
json.EmitCompact = 0
Response.Write "<pre>" & Server.HTMLEncode( json.Emit()) & "</pre>"
' Send the POST to:
' https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}:append?valueInputOption=USER_ENTERED
success = http.SetUrlVar("spreadsheetId","1_SO2L-Y6nCayNpNppJLF0r9yHB2UnaCleGCKeE4O0SA")
success = http.SetUrlVar("range","Sheet1!A1:B5")
' resp is a Chilkat.HttpResponse
Set resp = http.PostJson("https://sheets.googleapis.com/v4/spreadsheets/{$spreadsheetId}/values/{$range}:append?valueInputOption=USER_ENTERED",json.Emit())
If (http.LastMethodSuccess <> 1) Then
Response.Write "<pre>" & Server.HTMLEncode( http.LastErrorText) & "</pre>"
Response.End
End If
Response.Write "<pre>" & Server.HTMLEncode( "response status code = " & resp.StatusCode) & "</pre>"
Response.Write "<pre>" & Server.HTMLEncode( "response JSON = " & resp.BodyStr) & "</pre>"
' 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:
' 
%>
</body>
</html>
|
