|
PxPlus Google Sheets Object |
The PxPlus Google Sheets object provides the ability to work with Google Workspace® Sheets, a cloud-based spreadsheet application. This object consists of various properties and methods that are used to interact with Google Sheets spreadsheets. See Properties and Methods below.
The Google Sheets object allows more than one spreadsheet to be opened at one time, and each spreadsheet can consist of one or multiple sheets.
(The PxPlus Google Sheets object was added in PxPlus 2021.)
The following terms are used in Google Sheets:
|
|
Sheet |
A single page in which data is entered into grid columns and rows. |
|
|
Column |
Cells that are stacked vertically in a sheet. Column headers are labelled with alphabetical characters. |
|
|
Row |
Cells that extend horizontally in a sheet. Row headers are labelled with numbers. |
|
|
Cell |
The single point at which a column and row intersect (A1, B2, C4, etc.). A cell can contain a single piece of data. |
|
|
Range |
A block of selected or highlighted cells (e.g. the range A3:B4 includes the cells A3, A4, B3 and B4). |
|
|
Spreadsheet |
Cloud-based file consisting of a collection of individual sheets that have been saved. |
To instantiate the Google Sheets object using the handle sheets_obj (where sheets_obj could be any numeric variable), enter the following command:
sheets_obj=NEW("*obj/GoogleSheets",client_ID$,client_secret$[,login_token$])
The constructor requires a Client ID and Client Secret, both of which must be obtained from Google via the Google API Console. See Google API App Setup for detailed steps.
The login token can be optionally used to avoid having to select a Google account and login more than once. See Google Authentication for information on how to get a login_token$.
To access any of the available properties and/or methods, the Google Sheets object handle (sheets_obj) is used, followed by an ' (apostrophe) and the property or method (with the desired parameters).
Examples:
sheet=sheets_obj'ACTIVE_SPREADSHEET
retVal=sheets_obj'SetSpreadsheet(3)
During object instantiation, the user will be asked to select and login to a Google account and allow PxPlus access to it via their default Web browser. When this process is completed, the Login( ) method must be run to complete login to Google Sheets.
Successful login can be confirmed via the IsLoggedIn( ) method. If successfully logged in, then spreadsheets can be read, modified and saved to the local file system.
Once logged in to Google Sheets, the LOGIN_TOKEN$ property can be accessed and saved to avoid having to select and login to a Google account, allow access, and run the Login( ) method again. The login token can either be hard-coded into an encrypted program or saved to an encrypted file. The next time the Google Sheets objects gets instantiated, just include the saved login token and it will be logged into the same Google account automatically.
After one hour of inactivity, Google automatically expires a login. If logged in and Google expires the login, the next time you run a method, the object will automatically re-login. This may be noticeable if a method takes longer than usual to complete.
Google Sheets spreadsheets are stored in your Google Drive cloud storage, not as local files. Google Drive supports directories and sub-directories. All methods of this object that support a spreadsheet path allow the Google Drive directories and the spreadsheet name to be specified so that spreadsheets can be organized more easily. For example, to create a new spreadsheet "ABC123456" in the Google Drives directory "Clients", the following method can be used:
CreateSpreadsheet("Clients\ABC123456")
All modifications made to a Google Sheets spreadsheet are automatically saved. There are no Save methods. Modifications should not be made unless they are to be saved. If the original unmodified spreadsheet needs to be preserved, create a copy and modify it.
To work with a local spreadsheet, upload it to Google Sheets by using the UploadSpreadsheet( ) method. This method converts the local spreadsheet file into a Google Sheets spreadsheet, allowing you to work with it using this object. Supported file types that can be uploaded and converted are Microsoft Excel (.xls, .xlsx, .xlt), OpenDocument spreadsheet (.ods), CSV (.csv), TSV (.tsv) and plain text (.txt).
To download a Google Sheets spreadsheet, use the ExportSpreadsheet( ) method. This method converts the Google Sheets spreadsheet into a local spreadsheet file. The type of spreadsheet is determined by the file extension specified in the output path. Supported file types that a spreadsheet can be converted to and downloaded are Microsoft Excel (.xls, .xlsx, .xlt), Open Office sheet (.ods), HTML (.htm, .html), zipped HTML (.zip), CSV (.csv), TSV (.tsv), plain text (.txt) and PDF (.pdf).
The properties used by the Google Sheets object are listed below.
|
Property |
Description |
|
(Read Only) The active range string for the active sheet. Used in conjunction with setting fonts/colors, reading and writing when not specifying a range. When no active range is defined, ACTIVE_RANGE$ is an empty string "". This property is set using the SetRange Methods. Note: | |
|
(Read Only) Index (1-based) to the active sheet. This is the active sheet on the active spreadsheet. Returns 0 if there are no open spreadsheets. A newly opened spreadsheet will default the active sheet to 1. See SetSheet( ) method. | |
|
(Read Only) Index (1-based) of the active open spreadsheet. See SetSpreadsheet( ) method. If the ACTIVE_SPREADSHEET is not set with SetSpreadsheet( ) method, it is always the last opened spreadsheet/highest index number. Returns '0' if no spreadsheets are opened. | |
|
Defaults to '0' for case insensitive searches. Set to '1' for case sensitive searches. See FindReplaceAll( ) method. | |
|
LAST_COLUMN |
(Read Only) Column number of the last column of data in the active spreadsheet (ACTIVE_SHEET). |
|
LAST_ROW |
(Read Only) Row number of the last row of data in the active spreadsheet (ACTIVE_SHEET). |
|
Login token (also known as an Oauth2 refresh token) that can be used when instantiating the object to avoid having to login again. | |
|
Defaults to '0' for partial matches within cell contents. Set to '1' to force matches on the entire cell contents. See FindReplaceAll( ) method. | |
|
Delimiter used on various Read$ and Write methods to separate individual cell values. Defaults to the standard PxPlus field delimiter SEP ($8A$). See Cell Values Methods. | |
|
SPREADSHEETS_COUNT |
(Read Only) Number of spreadsheets opened. |
The methods used by the Google Sheets object are listed below. To make it easier to locate information for a particular method, this list has been broken into the following smaller groups:
|
|
For logging into Google and confirming logged in status | |
|
|
For creating, opening, exporting, uploading, setting and closing a spreadsheet | |
|
|
For creating and setting a sheet | |
|
|
For setting a range | |
|
|
For finding, replacing, reading and writing cell values | |
|
|
For setting text color, fill (background) color, font and number format | |
|
|
For deleting and inserting columns | |
|
|
For deleting and inserting rows |
Common Parameters
The following is a list of some of the common parameters used by multiple methods of the Google Sheets object:
|
Parameter |
Description |
|
spreadsheet |
1-based index number of the open Google Sheets spreadsheet. The index is set in the list of sequentially opened spreadsheets. If SpreadsheetA, SpreadsheetB and SpreadsheetC were opened in that order, the corresponding indexes would be 1, 2 and 3. If SpreadsheetA was closed at index 1, then SpreadsheetB and SpreadsheetC would now have indexes 1 and 2 respectively. |
|
spreadsheet_fileID$ |
Every spreadsheet in a Google Drive cloud has a unique file ID. The file ID is displayed in the address bar when a Google Sheets spreadsheet is opened in a Web browser. Since it is possible to have more than one spreadsheet with the same path, the file ID is used to specify a particular spreadsheet. This parameter is used by methods that end with ByID. |
|
spreadsheet_path$ |
The Google Sheets path consists of any directories and a spreadsheet name. It can be used as a simple and readable method to specify a spreadsheet. This parameter is used by methods that end with ByPath. Note: |
|
sheet |
1-based index number of a specific sheet in a spreadsheet. The index is set in the list of sequentially opened sheets. |
|
sheet_name$ |
Name of a specific sheet in a spreadsheet. |
|
cells$ |
Range of cells to be considered. Example: To use a previously named range, cells$ would be set to the name of the range itself (e.g. "header_row"). Note: |
|
Authentication Methods |
Description |
|
Returns '1' if logged into Google Sheets. Returns '0' if not yet logged into Google Sheets. Note: | |
|
During object instantiation, the user will be asked to select a Google account and allow access to it. When this process is completed, run this method to complete login to Google Sheets. Returns '1' if successful. Returns '0' if any error is encountered while logging in. |
|
Spreadsheet Methods |
Description |
|
CloseSpreadsheet( ) CloseSpreadsheet(spreadsheet) CloseSpreadsheetByID(spreadsheet_fileID$) CloseSpreadsheetByPath(spreadsheet_path$) |
Closes the spreadsheet specified by an index number, a Google Sheets file ID, or a Google Sheets path. If no spreadsheet is specified, the active spreadsheet will be closed. If you close the ACTIVE_SPREADSHEET, the ACTIVE_SPREADSHEET is set to the last opened spreadsheet still open; i.e. the highest spreadsheet index or 0 if no spreadsheets are left opened. The ACTIVE_SHEET is also reset to 1. Note:
|
|
CloseSpreadsheets( ) |
Closes all open spreadsheets. Returns '1' if successful. Returns '0' if any error is encountered while closing the spreadsheets. |
|
CopySpreadsheet(copy_path$) CopySpreadsheet(copy_path$,spreadsheet) CopySpreadsheetByID(copy_path$,spreadsheet_fileID$) CopySpreadsheetByPath(copy_path$,spreadsheet_path$) |
Makes a copy with the given path of the spreadsheet specified by an open spreadsheet index number, a Google Sheets file ID, or a Google Sheets path. If no spreadsheet is specified, the active spreadsheet will be copied. Returns '1' if successful. Returns '0' if any error is encountered while copying the spreadsheet. |
|
CreateSpreadsheet(spreadsheet_path$) |
Creates a new blank Google Sheets spreadsheet with the path spreadsheet_path$. The new spreadsheet is opened and becomes the ACTIVE_SPREADSHEET. If the spreadsheet_path$ parameter is null, the spreadsheet is named "Untitled". Important Note:
|
|
DeleteSpreadsheet( ) DeleteSpreadsheet(spreadsheet) DeleteSpreadsheetByID(spreadsheet_fileID$) DeleteSpreadsheetByPath(spreadsheet_path$) |
Deletes the spreadsheet from Google Sheets specified by an open spreadsheet index number, a Google Sheets file ID, or a Google Sheets path. If no spreadsheet is specified, the active spreadsheet will be deleted. The spreadsheet is closed as well. Returns '1' if successful. Returns '0' if any error is encountered while deleting the spreadsheet. |
|
ExportSpreadsheet(output_path$) ExportSpreadsheet(output_path$,spreadsheet) ExportSpreadsheetByID(output_path$,spreadsheet_fileID$) ExportSpreadsheetByPath(output_path$,spreadsheet_path$) |
Converts the ACTIVE_SPREADSHEET or the spreadsheet specified by an open spreadsheet index number, a Google Sheets file ID, or a Google Sheets path from a Google Sheets spreadsheet to a spreadsheet file and then downloads the file to the local file system. The type of local spreadsheet file and the path of the file is determined by output_path$. For supported output_path$ file types, see Cloud-Based Spreadsheets. Returns '1' if successful. Returns '0' if any error is encountered while converting and downloading the spreadsheet. |
|
GetFileID$( ) GetFileID$(spreadsheet) |
Returns a Google Sheets file ID of the spreadsheet specified by an open spreadsheet index number. If no spreadsheet is specified, the Google Sheets file ID of the active spreadsheet is returned. Before getting a file ID, the file must be opened first. Returns "" if unable to locate the spreadsheet. |
|
GetPath$( ) GetPath$(spreadsheet) |
Returns a Google Sheets path of the spreadsheet specified by an open spreadsheet index number. If no spreadsheet is specified, the Google Sheets path of the active spreadsheet is returned. Returns "" if unable to locate the spreadsheet. |
|
OpenSpreadsheetByID(spreadsheet_fileID$) OpenSpreadsheetByPath(spreadsheet_path$) |
Opens the Google Sheets spreadsheet specified by either spreadsheet_fileID$ or spreadsheet_path$. After opening a spreadsheet, the ACTIVE_SPREADSHEET property is set to the spreadsheet index corresponding to the spreadsheet just opened. Note:
|
|
SetSpreadsheetByID(spreadsheet_fileID$) SetSpreadsheetByPath(spreadsheet_path$) |
Sets the active spreadsheet index ACTIVE_SPREADSHEET to the spreadsheet specified by an open spreadsheet index number, a Google Sheets file ID, or a Google Sheets path. For a spreadsheet to be set as active, the spreadsheet must be opened. Note:
|
|
Upload a local spreadsheet file input_path$ to Google Sheets and convert to Google Sheets spreadsheet. The new Google Sheets spreadsheet is given the specified path. For supported input_path$ file types, see Cloud-Based Spreadsheets. Returns '1' if successful. Returns '0' if any error is encountered while uploading the spreadsheet. |
|
Sheet Methods |
Description |
|
CreateSheet(sheet_name$) CreateSheet(sheet_name$,tab_index) |
Creates a new sheet (sheet_name$) in the ACTIVE_SPREADSHEET. If no tab_index is specified, the new sheet will be inserted before the ACTIVE_SHEET. If a tab_index is specified, the sheet will be inserted as the sheet index specified. Specifying a tab_index higher than the current number of sheets will create the new sheet as the last sheet. If the sheet_name$ parameter is null, the default sheet name will be used (i.e. 'SheetX' where X is an incremental number to avoid sheet name conflicts). Returns '1' if successful. Returns '0' if unable to add the sheet. |
|
SetSheet(sheet) SetSheet(sheet_name$) |
Sets the active sheet index ACTIVE_SHEET to the sheet specified by either an index number or a simple sheet name (e.g. "Sheet1"). Returns '1' if successful. Returns '0' if unable to locate the sheet. |
|
Range Methods |
Description |
|
AddNamedRange(name$,cells$) AddNamedRange(name$,cells$,sheet) AddNamedRange(name$,cells$,sheet_name$) |
Creates a named range with the specified name$ and range (cells$). Note:
|
|
DeleteNamedRange(name$) |
Deletes a previously created named range (name$). Note:
|
|
SetRange(cells$,sheet) SetRange(cells$,sheet_name$) |
Sets the active range ACTIVE_RANGE$. When no sheet is specified, the range will be set on the active sheet or the sheet specified by the named range. Pass either a sheet index number or a simple sheet name to set the active range on a different sheet. Specify the range in the cells$ variable (e.g. "A12" or "A2:D5" or range_name$). Returns '1' if successful. Returns '0' if unable to locate the sheet or set the range. |
|
SetRangeColumn(col,sheet) SetRangeColumn(col,sheet_name$) SetRangeColumn(col$) SetRangeColumn(col$,sheet) SetRangeColumn(col$,sheet_name$) |
Sets the active range ACTIVE_RANGE$ to a particular column number (col) or letter (col$). When no sheet is specified, the range will be set to a column on the active sheet or the sheet specified by the named range. Pass either a sheet index number or a simple sheet name to set the active range on a different sheet. Returns '1' if successful. Returns '0' if unable to locate the sheet or set the range. |
|
SetRangeRow(row,sheet) SetRangeRow(row,sheet_name$) |
Sets the active range ACTIVE_RANGE$ to a particular row (row). When no sheet is specified, the range will be set to a row on the active sheet or the sheet specified by the named range. Pass either a sheet index number or a simple sheet name to set the active range on a different sheet. Returns '1' if successful. Returns '0' if unable to locate the sheet or set the range. |
|
Cell Values Methods |
Description |
|
FindReplaceAll(find$,replace$) FindReplaceAll(find$,replace$,sheet) FindReplaceAll(find$,replace$,sheet_name$) |
Finds cells with the value find$ and replaces all occurrences with the string replace$. When only the find$ and replace$ parameters are passed, the Find/Replace is performed on the active sheet. Pass either a sheet index number or a simple sheet name to perform the Find/Replace on a sheet other than the active sheet without changing the ACTIVE_SHEET property. To perform the Find/Replace on all opened sheets, pass a sheet_name$ of "ALL" (case insensitive). Searches will be case insensitive by default. For case sensitive searches, set the CASE_SENSITIVE_SEARCH property to '1'. By default, the find$ value will be replaced if it is located anywhere within a cell value. Set the MATCH_ENTIRE_CELL property to '1' to replace text only if the entire cell contents is equal to the find$ value. Returns '1' if successful. Returns '0' if unable to Find/Replace on any of the sheets specified or if the find$ value was not found. When finding/replacing on all sheets: Returns '-1' if only able to perform Find/Replace on some of the sheets successfully. |
|
Read$( ) Read$(cells$) Read$(cells$,sheet) Read$(cells$,sheet_name$) |
Reads data from a sheet. When no sheet is specified, the data will be read from the active sheet or the sheet specified by the named range. Pass either a sheet index number or a simple sheet name to read data from a different sheet without changing the ACTIVE_SHEET property. When no range is specified or cells$ is an empty string "", data is read from the active range. Pass a value in the cells$ parameter (e.g. "A12" or "A2:D5" or range_name$) to specify a different range without changing the ACTIVE_RANGE$ property. When the cells$ value corresponds to a single cell in the sheet, a string value is returned. When the cells$ value corresponds to a range of 2 or more cells, a SEP$ delimited string is returned. If a problem is encountered, a null string is returned. |
|
Write(cell_values$) Write(cell_values$,cells$) Write(cell_values$,cells$,sheet) Write(cell_values$,cells$,sheet_name$) |
Writes data to a sheet. When no sheet is specified, the data will be written to the active sheet or the sheet specified by the named range. Pass either a sheet index number or a simple sheet name to write data to a different sheet without changing the ACTIVE_SHEET property. When no range is specified or cells$ is an empty string "", data is written to the active range. Pass a value in the cells$ parameter (e.g. "A12" or "A2:D5" or range_name$) to specify a different range without changing the ACTIVE_RANGE$ property. When writing data to a single cell, cell_values$ should contain a string value. When writing to a range of cells, cell_values$ should contain a string value delimited with a delimiter corresponding to the current value in the SEP$ property. Example: When SEP$=SEP: Returns '0' if the data was not written. Returns '1' if the data was written successfully and the number of specified cell values (cell_values$) matched the number of cells in the specified range (cells$). Returns '-1' if the data was written but the number of specified cell values (cell_values$) did not match the number of cells in the specified range (cells$). In this case, one of the following instances can occur: • If the number of cell values (cell_values$) is greater than the number of cells in the specified range (cells$), the remaining cell values not written to a cell will be ignored. • If the number of cell values (cell_values$) is fewer than the number of cells in the specified range (cells$), null values will be written into the remaining cells in that range to which no cell values were assigned. |
|
Cell Formatting Methods |
Description |
|
SetColor(color_index) SetColor(color$) SetColor(color_index,cells$) SetColor(color$,cells$) SetColor(color_index,cells$,sheet) SetColor(color$,cells$,sheet) SetColor(color_index,cells$,sheet_name$) SetColor(color$,cells$,sheet_name$) |
Sets the text color for a range of cells in a sheet. When no sheet is specified, the text color will be set on the active sheet or the sheet specified by the named range. Pass either a sheet index number or a simple sheet name to define the text color on a different sheet without changing the ACTIVE_SHEET property. When no range is specified or cells$ is an empty string "", the text color will be set in the active range. Pass a value in the cells$ parameter (e.g. "A12" or "A2:D5" or range_name$) to specify a different range without changing the ACTIVE_RANGE$ property. The text color may be passed as one of the following: • A color index (numeric) • An RGB value in the format "RGB:rrr,ggg,bbb" (string) • "HSL:hhh,sss,lll" (string) • Hex "#nnnnnn" (string) • One of the 16 basic PxPlus named colors, such as 'Red', 'Dark Blue' or 'Light Cyan' (string) • A color-blended string, such as 'Red'+'Yellow' • Dynamic color lightening, such as "Red*50" (string) Returns '1' if successful. Returns '0' if unable to determine the sheet or range. |
|
SetFillColor(color_index) SetFillColor(color$) SetFillColor(color_index,cells$) SetFillColor(color$,cells$) SetFillColor(color_index,cells$,sheet) SetFillColor(color$,cells$,sheet) SetFillColor(color_index,cells$,sheet_name$) SetFillColor(color$,cells$,sheet_name$) |
Sets the fill (background) color for a range of cells in a sheet. When no sheet is specified, the fill color will be set on the active sheet or the sheet specified by the named range. Pass either a sheet index number or a simple sheet name to define the fill color on a different sheet without changing the ACTIVE_SHEET property. When no range is specified or cells$ is an empty string "", the fill color will be set in the active range. Pass a value in the cells$ parameter (e.g. "A12" or "A2:D5" or range_name$) to specify a different range without changing the ACTIVE_RANGE$ property. The fill (background) color may be passed as one of the following: • A color index (numeric) • An RGB value in the format "RGB:rrr,ggg,bbb" (string) • "HSL:hhh,sss,lll" (string) • Hex "#nnnnnn" (string) • One of the 16 basic PxPlus named colors, such as 'Red', 'Dark Blue' or 'Light Cyan' (string) • A color-blended string, such as 'Red'+'Yellow' • Dynamic color lightening, such as "Red*50" (string) Returns '1' if successful. Returns '0' if unable to determine the sheet or range. |
|
SetFont(font$) SetFont(font$,cells$) SetFont(font$,cells$,sheet) SetFont(font$,cells$,sheet_name$) |
Sets font information (font name, font size, font style) for a range of cells in a sheet. When no sheet is specified, the font will be set on the active sheet or the sheet specified by the named range. Pass either a sheet index number or a simple sheet name to define the font on a different sheet without changing the ACTIVE_SHEET property. When no range is specified or cells$ is an empty string "", the font will be set in the active range. Pass a value in the cells$ parameter (e.g. "A12" or "A2:D5" or range_name$) to specify a different range without changing the ACTIVE_RANGE$ property. The font$ variable can pass font name, font size and font style (Bold, Italic, etc.), separated by commas. The font name is case sensitive. If a unsupported font name is specified, Arial is used. Note:
|
|
SetNumFormat(num_type$,num_pattern$) SetNumFormat(num_type$,num_pattern$,cells$) SetNumFormat(num_type$,num_pattern$,cells$,sheet) SetNumFormat(num_type$,num_pattern$,cells$,sheet_name$) |
Sets the number format for a range of cells in a sheet. When no sheet is specified, the number format will be set on the active sheet or the sheet specified by the named range. Pass either a sheet index number or a simple sheet name to define the number format on a different sheet without changing the ACTIVE_SHEET property. When no range is specified or cells$ is an empty string "", the number format will be set in the active range. Pass a value in the cells$ parameter (e.g. "A12" or "A2:D5" or range_name$) to specify a different range without changing the ACTIVE_RANGE$ property. num_type$ may be passed as one of the following (you must pass a valid num_type$): "TEXT" num_pattern$ is a string (e.g. "###0.000") that defines how it is displayed. (Leave blank if you just want to set the num_type$.) Returns '1' if successful. Returns '0' if unable to determine the sheet or range. (The SetNumFormat method was added in PxPlus 2023.) |
|
Column Methods |
Description |
|
DeleteColumn(col) DeleteColumn(col,sheet) DeleteColumn(col,sheet_name$) DeleteColumn(col$) DeleteColumn(col$,sheet) DeleteColumn(col$,sheet_name$) |
Deletes column number col (or column letter col$) in the sheet specified by either a sheet index number or a simple sheet name. If no sheet is specified, the column is deleted from the active sheet. Returns '1' if successful. Returns '0' if unable to locate the sheet and column. |
|
InsertColumn(col) InsertColumn(col,sheet) InsertColumn(col,sheet_name$) InsertColumn(col$) InsertColumn(col$,sheet) InsertColumn(col$,sheet_name$) |
Inserts a new blank column number col (or column letter col$) in the sheet specified by either a sheet index number or a simple sheet name. If no sheet is specified, the column is inserted into the active sheet. Returns '1' if successful. Returns '0' if unable to locate the sheet and column. |
|
SetColumnWidth(width,cells$) SetColumnWidth(width,cells$,sheet) SetColumnWidth(width,cells$,sheet_name$) |
Sets the width of all columns in a specified range. If no sheet is specified, the active sheet is used or the sheet specified by the named range. If no range is specified (cells$), the active range is used. Returns '1' if successful. Returns '0' if unable to locate the specified sheet or range. |
|
Row Methods |
Description |
|
DeleteRow(row) DeleteRow(row,sheet) DeleteRow(row,sheet_name$) |
Deletes row number row in the sheet specified by either a sheet index number or a simple sheet name. If no sheet is specified, the row is deleted from the active sheet. Returns '1' if successful. Returns '0' if unable to locate the sheet and row. |
|
InsertRow(row) InsertRow(row,sheet) InsertRow(row,sheet_name$) |
Inserts a new blank row number row in the sheet specified by either a sheet index number or a simple sheet name. If no sheet is specified, the row is inserted into the active sheet. Returns '1' if successful. Returns '0' if unable to locate the sheet and row. |
This example program tests the Google Sheets object.
! Test Google Sheets object
!
! instantiate object
x=NEW("*obj/GoogleSheets",clientID$,clientSecret$)
!
! Wait for user to complete Google sign-in and allow PxPlus access
INPUT "Press any key to continue after logging into Google account and allowing PxPlus access:",*;PRINT ""
!
! Complete the Google Sheets login
x'Login()
!
! Open spreadsheet
path$="Commissions/JuneCommissions"
if x'openSpreadsheetByPath(path$)=0 then MSGBOX "Couldn't find "+path$
!
! Open another Spreadsheet
path2$="Commissions/JulyCommissions"
if x'openSpreadsheetByPath(path2$)=0 then MSGBOX "Couldn't find "+path2$
!
! Set sheet by name
if x'SetSheet("Sheet1")=0 then MSGBOX "Couldn't find Sheet1"
!
! Read a value in cell C2 of active sheet
c$=x'read$("C2")
!
! Write a value to cell C3 in active sheet
if x'write("*THIS HAS BEEN CHANGED!*","C3")=0 then MSGBOX "Couldn't complete the 1st write"
!
! Read row 3 in sheet 2
if x'SetRange("A3:E3",2)=0 then MSGBOX "Couldn't set the active range"
range$=x'read$()
!
! Write to row 3 (active range in sheet 2)
new_range$="*Column 1*"+SEP+"*Column 2*"+SEP+"*Column3*"+SEP+"*Column 4*"+SEP+"*Column 5*"+SEP
if x'write(new_range$)=0 then MSGBOX "Couldn't complete the 2nd write"
!
! Read column 4 (D) in sheet 1
row$=x'read$("D1:D10",1)
!
! Switch back to first workbook
if x'SetSpreadsheet(1)=0 then MSGBOX "Couldn't find spreadsheet1"
!
! Set a range
if x'SetRange("A1:D4")=0 then MSGBOX "Couldn't set the active range"
!
! Set Font for active range and sheet
if x'SetFont("Arial,16,Bold")=0 then MSGBOX "Couldn't set the font"
!
! Create a 'named' range for the first row
if x'AddNamedRange("Headers","A1:Z1")=0 then MSGBOX "Couldn't add a named range"
!
! Set active range text colour to red
if x'SetColor(5)=0 then MSGBOX "Couldn't change text color"
!
! Insert a row #5 in active sheet
if x'InsertRow(5)=0 then MSGBOX "Couldn't insert a new row"
!
! Set the fill color to yellow for a different range in the third sheet
if x'SetFillColor("light yellow","B3:F8",3)=0 then MSGBOX "Couldn't change fill (background) color"
!
! Set the text for column "C" to blue, Courier New
if x'SetRangeColumn("C")=0 then MSGBOX "Failed setting a column range"
if x'SetColor(4)=0 then MSGBOX "Couldn't change text color"
if x'SetFont("Courier New")=0 then MSGBOX "Couldn't set the font"
!
! Export current spreadsheet as a CSV
if x'ExportSpreadsheet("C:\Users\User\Documents\JuneCommissions.csv")=0 then MSGBOX "Couldn't export to csv"
!
! Close spreadsheets
if x'CloseSpreadsheets()=0 then MSGBOX "Couldn't close spreadsheets"
!
! Drop object
DROP OBJECT x
END
PxPlus Google Docs Object
PxPlus Google Drive Object
Google Workspace® is a registered trademark of Google LLC