Delphi ActiveX

Google Sheets - Append Values to an Existing Spreadsheet

See more Google Sheets Examples

Appends values to an existing Google spreadsheet. Delphi ActiveX

uses
    Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
    Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.StdCtrls, Chilkat_TLB;

...

procedure TForm1.Button1Click(Sender: TObject);
var
success: Integer;
jsonToken: TChilkatJsonObject;
http: TChilkatHttp;
json: TChilkatJsonObject;
resp: TChilkatHttpResponse;

begin
success := 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 := TChilkatJsonObject.Create(Self);
success := jsonToken.LoadFile('qa_data/tokens/googleSheets.json');
if (jsonToken.HasMember('access_token') = 0) then
  begin
    Memo1.Lines.Add('No access token found.');
    Exit;
  end;
http := TChilkatHttp.Create(Self);
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"
//     ]
//   ]
// }

json := TChilkatJsonObject.Create(Self);
json.UpdateString('range','Sheet1!A1:B5');
json.UpdateString('majorDimension','ROWS');

json.I := 0;
json.J := 1;
json.UpdateString('values[i][j]','Paint');
json.J := 1;
json.UpdateString('values[i][j]','$100');

json.I := 1;
json.J := 0;
json.UpdateString('values[i][j]','Brakes');
json.J := 1;
json.UpdateString('values[i][j]','$100');

json.I := 2;
json.J := 0;
json.UpdateString('values[i][j]','Totals');
json.J := 1;
json.UpdateString('values[i][j]','$335.50');

json.EmitCompact := 0;
Memo1.Lines.Add(json.Emit());

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

http.SetUrlVar('spreadsheetId','1_SO2L-Y6nCayNpNppJLF0r9yHB2UnaCleGCKeE4O0SA');
http.SetUrlVar('range','Sheet1!A1:B5');
resp := TChilkatHttpResponse.Create(Self);
success := http.HttpJson('POST','https://sheets.googleapis.com/v4/spreadsheets/{$spreadsheetId}/values/{$range}:append?valueInputOption=USER_ENTERED',json.ControlInterface,'application/json',resp.ControlInterface);
if (success = 0) then
  begin
    Memo1.Lines.Add(http.LastErrorText);
    Exit;
  end;

Memo1.Lines.Add('response status code = ' + IntToStr(resp.StatusCode));
Memo1.Lines.Add('response JSON = ' + resp.BodyStr);

// 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;