DataFlex
DataFlex
Google Sheets - Create a New Spreadsheet
See more Google Sheets Examples
Demonstrates how to create a new and empty spreadsheet.Chilkat DataFlex Downloads
Use ChilkatAx-win32.pkg
Procedure Test
Boolean iSuccess
Variant vJson
Token Handle hoJsonToken
Handle hoHttp
Variant vJson
Handle hoJson
Variant vResp
Handle hoResp
Integer i
Integer iCount_i
String sSpreadsheetId
String sPropertiesTitle
String sPropertiesLocale
String sPropertiesAutoRecalc
String sPropertiesTimeZone
Integer iPropertiesDefaultFormatBackgroundColorRed
Integer iPropertiesDefaultFormatBackgroundColorGreen
Integer iPropertiesDefaultFormatBackgroundColorBlue
Integer iPropertiesDefaultFormatPaddingTop
Integer iPropertiesDefaultFormatPaddingRight
Integer iPropertiesDefaultFormatPaddingBottom
Integer iPropertiesDefaultFormatPaddingLeft
String sPropertiesDefaultFormatVerticalAlignment
String sPropertiesDefaultFormatWrapStrategy
String sPropertiesDefaultFormatTextFormatFontFamily
Integer iPropertiesDefaultFormatTextFormatFontSize
Boolean iPropertiesDefaultFormatTextFormatBold
Boolean iPropertiesDefaultFormatTextFormatItalic
Boolean iPropertiesDefaultFormatTextFormatStrikethrough
Boolean iPropertiesDefaultFormatTextFormatUnderline
String sSpreadsheetUrl
Integer iPropertiesSheetId
Integer iPropertiesIndex
String sPropertiesSheetType
Integer iPropertiesGridPropertiesRowCount
Integer iPropertiesGridPropertiesColumnCount
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
// 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:
Get Create (RefClass(cComChilkatJsonObject)) To hoJson
If (Not(IsComObjectCreated(hoJson))) Begin
Send CreateComObject of hoJson
End
Get ComUpdateString Of hoJson "sheets[0].properties.title" "Sample Tab" To iSuccess
Get ComUpdateString Of hoJson "properties.title" "Create Spreadsheet using Sheets API v4" To iSuccess
// Send the POST to create the new Google spreadsheet.
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" 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
Showln "response JSON:"
Get ComBodyStr Of hoResp To sTemp1
Get ComLoad Of hoJson sTemp1 To iSuccess
Set ComEmitCompact Of hoJson To False
Get ComEmit Of hoJson To sTemp1
Showln sTemp1
// 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"
// }
//
Get ComStringOf Of hoJson "spreadsheetId" To sSpreadsheetId
Get ComStringOf Of hoJson "properties.title" To sPropertiesTitle
Get ComStringOf Of hoJson "properties.locale" To sPropertiesLocale
Get ComStringOf Of hoJson "properties.autoRecalc" To sPropertiesAutoRecalc
Get ComStringOf Of hoJson "properties.timeZone" To sPropertiesTimeZone
Get ComIntOf Of hoJson "properties.defaultFormat.backgroundColor.red" To iPropertiesDefaultFormatBackgroundColorRed
Get ComIntOf Of hoJson "properties.defaultFormat.backgroundColor.green" To iPropertiesDefaultFormatBackgroundColorGreen
Get ComIntOf Of hoJson "properties.defaultFormat.backgroundColor.blue" To iPropertiesDefaultFormatBackgroundColorBlue
Get ComIntOf Of hoJson "properties.defaultFormat.padding.top" To iPropertiesDefaultFormatPaddingTop
Get ComIntOf Of hoJson "properties.defaultFormat.padding.right" To iPropertiesDefaultFormatPaddingRight
Get ComIntOf Of hoJson "properties.defaultFormat.padding.bottom" To iPropertiesDefaultFormatPaddingBottom
Get ComIntOf Of hoJson "properties.defaultFormat.padding.left" To iPropertiesDefaultFormatPaddingLeft
Get ComStringOf Of hoJson "properties.defaultFormat.verticalAlignment" To sPropertiesDefaultFormatVerticalAlignment
Get ComStringOf Of hoJson "properties.defaultFormat.wrapStrategy" To sPropertiesDefaultFormatWrapStrategy
Get ComStringOf Of hoJson "properties.defaultFormat.textFormat.fontFamily" To sPropertiesDefaultFormatTextFormatFontFamily
Get ComIntOf Of hoJson "properties.defaultFormat.textFormat.fontSize" To iPropertiesDefaultFormatTextFormatFontSize
Get ComBoolOf Of hoJson "properties.defaultFormat.textFormat.bold" To iPropertiesDefaultFormatTextFormatBold
Get ComBoolOf Of hoJson "properties.defaultFormat.textFormat.italic" To iPropertiesDefaultFormatTextFormatItalic
Get ComBoolOf Of hoJson "properties.defaultFormat.textFormat.strikethrough" To iPropertiesDefaultFormatTextFormatStrikethrough
Get ComBoolOf Of hoJson "properties.defaultFormat.textFormat.underline" To iPropertiesDefaultFormatTextFormatUnderline
Get ComStringOf Of hoJson "spreadsheetUrl" To sSpreadsheetUrl
Move 0 To i
Get ComSizeOfArray Of hoJson "sheets" To iCount_i
While (i < iCount_i)
Set ComI Of hoJson To i
Get ComIntOf Of hoJson "sheets[i].properties.sheetId" To iPropertiesSheetId
Get ComStringOf Of hoJson "sheets[i].properties.title" To sPropertiesTitle
Get ComIntOf Of hoJson "sheets[i].properties.index" To iPropertiesIndex
Get ComStringOf Of hoJson "sheets[i].properties.sheetType" To sPropertiesSheetType
Get ComIntOf Of hoJson "sheets[i].properties.gridProperties.rowCount" To iPropertiesGridPropertiesRowCount
Get ComIntOf Of hoJson "sheets[i].properties.gridProperties.columnCount" To iPropertiesGridPropertiesColumnCount
Move (i + 1) To i
Loop
End_Procedure