(DataFlex) Google Sheets - Create a New Spreadsheet

Demonstrates how to create a new and empty spreadsheet.

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

Use ChilkatAx-win32.pkg

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

    // 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 pvComObject of hoJson to vJson
    Get ComPostJson3 Of hoHttp "https://sheets.googleapis.com/v4/spreadsheets" "application/json" vJson To vResp
    If (IsComObject(vResp)) Begin
        Get Create (RefClass(cComChilkatHttpResponse)) To hoResp
        Set pvComObject Of hoResp To vResp
    End
    Get ComLastMethodSuccess Of hoHttp To bTemp1
    If (bTemp1 <> True) 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

    Send Destroy of hoResp

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