Pascal (Lazarus/Delphi)

Google Sheets - Create a New Spreadsheet

See more Google Sheets Examples

Demonstrates how to create a new and empty spreadsheet. Pascal (Lazarus/Delphi)

program ChilkatDemo;

// Demonstrates using the Chilkat Pascal wrapper via the C bridge DLL.
// Builds as a console application under Lazarus (FPC) or Delphi.

{$IFDEF FPC}
  {$MODE DELPHI}
{$ENDIF}
{$APPTYPE CONSOLE}

uses
  {$IFDEF UNIX}
  cthreads,
  {$ENDIF}
  SysUtils,
  CkDllLoader,
  Chilkat.Http,
  Chilkat.HttpResponse,
  Chilkat.JsonObject;

// ---------------------------------------------------------------------------

procedure RunDemo;
var
  success: Boolean;
  jsonToken: TJsonObject;
  http: THttp;
  json: TJsonObject;
  resp: THttpResponse;
  i: Integer;
  count_i: Integer;
  spreadsheetId: string;
  propertiesTitle: string;
  propertiesLocale: string;
  propertiesAutoRecalc: string;
  propertiesTimeZone: string;
  propertiesDefaultFormatBackgroundColorRed: Integer;
  propertiesDefaultFormatBackgroundColorGreen: Integer;
  propertiesDefaultFormatBackgroundColorBlue: Integer;
  propertiesDefaultFormatPaddingTop: Integer;
  propertiesDefaultFormatPaddingRight: Integer;
  propertiesDefaultFormatPaddingBottom: Integer;
  propertiesDefaultFormatPaddingLeft: Integer;
  propertiesDefaultFormatVerticalAlignment: string;
  propertiesDefaultFormatWrapStrategy: string;
  propertiesDefaultFormatTextFormatFontFamily: string;
  propertiesDefaultFormatTextFormatFontSize: Integer;
  propertiesDefaultFormatTextFormatBold: Boolean;
  propertiesDefaultFormatTextFormatItalic: Boolean;
  propertiesDefaultFormatTextFormatStrikethrough: Boolean;
  propertiesDefaultFormatTextFormatUnderline: Boolean;
  spreadsheetUrl: string;
  propertiesSheetId: Integer;
  propertiesIndex: Integer;
  propertiesSheetType: string;
  propertiesGridPropertiesRowCount: Integer;
  propertiesGridPropertiesColumnCount: Integer;

begin
  success := False;

  //  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 := TJsonObject.Create;
  success := jsonToken.LoadFile('qa_data/tokens/googleSheets.json');
  if (jsonToken.HasMember('access_token') = False) then
    begin
      WriteLn('No access token found.');
      Exit;
    end;
  http := THttp.Create;
  http.AuthToken := jsonToken.StringOf('access_token');

  //  Create the following JSON:
  //  The JSON code can be generated using this online tool:  Generate JSON create code
  //  {
  //    "sheets": [
  //      {
  //        "properties": {
  //          "title": "Sample Tab"
  //        }
  //      }
  //    ],
  //    "properties": {
  //      "title": "Create Spreadsheet using Sheets API v4"
  //    }
  //  }

  //  This code generates the above JSON:
  json := TJsonObject.Create;
  json.UpdateString('sheets[0].properties.title','Sample Tab');
  json.UpdateString('properties.title','Create Spreadsheet using Sheets API v4');

  //  Send the POST to create the new Google spreadsheet.
  resp := THttpResponse.Create;
  success := http.HttpJson('POST','https://sheets.googleapis.com/v4/spreadsheets',json,'application/json',resp);
  if (success = False) then
    begin
      WriteLn(http.LastErrorText);
      Exit;
    end;

  WriteLn('response status code = ' + resp.StatusCode);
  WriteLn('response JSON:');

  json.Load(resp.BodyStr);
  json.EmitCompact := False;
  WriteLn(json.Emit());

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

  //  {
  //    "spreadsheetId": "1ueEQu3WDBkIAOUhzLnY4zr6JO5SrJx0dQ-YkQlUVYD0",
  //    "properties": {
  //      "title": "Create Spreadsheet using Sheets API v4",
  //      "locale": "en_US",
  //      "autoRecalc": "ON_CHANGE",
  //      "timeZone": "Etc/GMT",
  //      "defaultFormat": {
  //        "backgroundColor": {
  //          "red": 1,
  //          "green": 1,
  //          "blue": 1
  //        },
  //        "padding": {
  //          "top": 2,
  //          "right": 3,
  //          "bottom": 2,
  //          "left": 3
  //        },
  //        "verticalAlignment": "BOTTOM",
  //        "wrapStrategy": "OVERFLOW_CELL",
  //        "textFormat": {
  //          "foregroundColor": {},
  //          "fontFamily": "arial,sans,sans-serif",
  //          "fontSize": 10,
  //          "bold": false,
  //          "italic": false,
  //          "strikethrough": false,
  //          "underline": false
  //        }
  //      }
  //    },
  //    "sheets": [
  //      {
  //        "properties": {
  //          "sheetId": 1629642057,
  //          "title": "Sample Tab",
  //          "index": 0,
  //          "sheetType": "GRID",
  //          "gridProperties": {
  //            "rowCount": 1000,
  //            "columnCount": 26
  //          }
  //        }
  //      }
  //    ],
  //    "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/1ueEQu3WDBkIAOUhzLnY4zr6JO5SrJx0dQ-YkQlUVYD0/edit"
  //  }
  //  

  spreadsheetId := json.StringOf('spreadsheetId');
  propertiesTitle := json.StringOf('properties.title');
  propertiesLocale := json.StringOf('properties.locale');
  propertiesAutoRecalc := json.StringOf('properties.autoRecalc');
  propertiesTimeZone := json.StringOf('properties.timeZone');
  propertiesDefaultFormatBackgroundColorRed := json.IntOf('properties.defaultFormat.backgroundColor.red');
  propertiesDefaultFormatBackgroundColorGreen := json.IntOf('properties.defaultFormat.backgroundColor.green');
  propertiesDefaultFormatBackgroundColorBlue := json.IntOf('properties.defaultFormat.backgroundColor.blue');
  propertiesDefaultFormatPaddingTop := json.IntOf('properties.defaultFormat.padding.top');
  propertiesDefaultFormatPaddingRight := json.IntOf('properties.defaultFormat.padding.right');
  propertiesDefaultFormatPaddingBottom := json.IntOf('properties.defaultFormat.padding.bottom');
  propertiesDefaultFormatPaddingLeft := json.IntOf('properties.defaultFormat.padding.left');
  propertiesDefaultFormatVerticalAlignment := json.StringOf('properties.defaultFormat.verticalAlignment');
  propertiesDefaultFormatWrapStrategy := json.StringOf('properties.defaultFormat.wrapStrategy');
  propertiesDefaultFormatTextFormatFontFamily := json.StringOf('properties.defaultFormat.textFormat.fontFamily');
  propertiesDefaultFormatTextFormatFontSize := json.IntOf('properties.defaultFormat.textFormat.fontSize');
  propertiesDefaultFormatTextFormatBold := json.BoolOf('properties.defaultFormat.textFormat.bold');
  propertiesDefaultFormatTextFormatItalic := json.BoolOf('properties.defaultFormat.textFormat.italic');
  propertiesDefaultFormatTextFormatStrikethrough := json.BoolOf('properties.defaultFormat.textFormat.strikethrough');
  propertiesDefaultFormatTextFormatUnderline := json.BoolOf('properties.defaultFormat.textFormat.underline');
  spreadsheetUrl := json.StringOf('spreadsheetUrl');
  i := 0;
  count_i := json.SizeOfArray('sheets');
  while (i < count_i) do
    begin
      json.I := i;
      propertiesSheetId := json.IntOf('sheets[i].properties.sheetId');
      propertiesTitle := json.StringOf('sheets[i].properties.title');
      propertiesIndex := json.IntOf('sheets[i].properties.index');
      propertiesSheetType := json.StringOf('sheets[i].properties.sheetType');
      propertiesGridPropertiesRowCount := json.IntOf('sheets[i].properties.gridProperties.rowCount');
      propertiesGridPropertiesColumnCount := json.IntOf('sheets[i].properties.gridProperties.columnCount');
      i := i + 1;
    end;



  jsonToken.Free;
  http.Free;
  json.Free;
  resp.Free;

end;

// ---------------------------------------------------------------------------

begin

  try
    RunDemo;
  except
    on E: Exception do
      WriteLn('Unhandled exception: ', E.ClassName, ': ', E.Message);
  end;

  WriteLn;
  {$IFDEF MSWINDOWS}
  WriteLn('Press Enter to exit...');
  ReadLn;
  {$ENDIF}
end.