# Google Workspace® Objects

PxPlus Google Sheets Object (*obj/GoogleSheets) |

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). A range of cells can be optionally given a name (e.g. the range of cells A4:F4 can be named "SalesTotal"). A named range is easier to understand and simpler to use when creating formulas that reference values in other cells. For example, instead of using cell references in the formula =SUM(A4:F4), the range name can be used instead, as in =SUM(SalesTotal). Any range names that have been defined are saved with the sheet.
| Spreadsheet | Cloud-based file consisting of a collection of individual sheets that have been saved.

## Instantiating the Object

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)

Google Authentication

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.

## Cloud-Based Spreadsheets

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).

## Properties and Methods

The properties used by the Google Sheets object are listed below.

Property | Description
ACTIVE_RANGE$ | (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: There is no active range on a newly opened sheet. It is good practice to either set the range (i.e. SetRange, SetRangeColumn or SetRangeRow) before assuming the default range on subsequent method calls or specify the range to use (cells$) in each individual method call.
ACTIVE_SHEET | (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.
ACTIVE_SPREADSHEET | (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.
CASE_SENSITIVE_SEARCH | 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$ | Login token (also known as an Oauth2 refresh token) that can be used when instantiating the object to avoid having to login again.
MATCH_ENTIRE_CELL | Defaults to '0' for partial matches within cell contents. Set to '1' to force matches on the entire cell contents. See FindReplaceAll( ) method.
SEP$ | 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:

| Authentication Methods | For logging into Google and confirming logged in status
| Spreadsheet Methods | For creating, opening, exporting, uploading, setting and closing a spreadsheet
| Sheet Methods | For creating and setting a sheet
| Range Methods | For setting a range
| Cell Values Methods | For finding, replacing, reading and writing cell values
| Cell Formatting Methods | For setting text color, fill (background) color, font and number format
| Column Methods | For deleting and inserting columns
| Row Methods | 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: This path consists of the directories and spreadsheet name from the Google Sheets cloud, not the path to a spreadsheet on the local file system.
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: "A12" would specify a single cell. "A2:D5" would denote a group of cells from column A, row 2 to column D, row 5. "A:B" would specify all of column A and column B. "2:2" would specify all of row 2. To use a previously named range, cells$ would be set to the name of the range itself (e.g. "header_row"). #### Note: The sheet is defined as part of the named range. If specifying a named range, a sheet does not need to be specified. If specifying a named range and a particular sheet, the sheet defined in the named range is used.

Authentication Methods

Authentication Methods | Description
IsLoggedIn( ) | Returns '1' if logged into Google Sheets. Returns '0' if not yet logged into Google Sheets. #### Note: After one hour of inactivity, Google Sheets will expire the login; however, this method will still return '1'.
Login( ) | 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

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: When closing a spreadsheet, any spreadsheet opened after the closed spreadsheet was opened (any spreadsheet with a higher index) will have its index shifted down 1. For example, if closing the spreadsheet with index 2, the spreadsheet at index 3 becomes index 2. Returns '1' if successful. Returns '0' if any error is encountered while closing the spreadsheet.
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: It is possible to have multiple spreadsheets with the same path. They have different file IDs. Returns '1' if successful. Returns '0' if any error is encountered while creating the spreadsheet.
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: When opening a spreadsheet, the index is set to the number of open spreadsheets. The first spreadsheet opened will be index 1, the second spreadsheet opened will be index 2, etc. Returns '1' if successful. Returns '0' if any error is encountered while opening the spreadsheet.
SetSpreadsheet(spreadsheet) 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: Setting a spreadsheet will also reset the ACTIVE_SHEET to the first sheet (index 1) and ACTIVE_RANGE$ to an empty string "". Returns '1' if successful. Returns '0' if unable to locate the spreadsheet.
UploadSpreadsheet(input_path$,spreadsheet_path$) | 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

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

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: A range name (name$) cannot contain any spaces. When no sheet is specified, the named range will be created using the active sheet. Pass either a sheet index number or a simple sheet name to define the named range using a different sheet. Once a named range is created, the name of the range (name$) may be used to refer to the range and sheet in any method where a range is specified (cells$). Returns '1' if successful. Returns '0' if not able to create the named range with the parameters provided.
DeleteNamedRange(name$) | Deletes a previously created named range (name$). #### Note: A range name (name$) cannot contain any spaces. Returns '1' if successful. Returns '0' if not able to delete the named range.
SetRange(cells$) 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) 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) 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

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: cell_values$ = "value one" + SEP + "value two" + SEP + "value three" + 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

Cell Formatting Methods | Description
AutoFitAll( ) AutoFitAll(sheet) AutoFitAll(sheet_name$) | Auto resize all column widths and row heights based on cell content in a sheet. When no sheet is specified, the auto resize will be done 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 auto resize on a different sheet without changing the ACTIVE_SHEET property. Should be used after the cells are populated to adjust column widths and row heights to fit the existing data. Returns '1' if successful. Returns '0' if unable to locate the sheet. (The AutoFitAll method was added in PxPlus 2025.)
AutoFitColumns( ) AutoFitColumns(cells$) AutoFitColumns(cells$,sheet) AutoFitColumns(cells$,sheet_name$) | Auto resize the column widths based on cell content for a range of cells in a given sheet. When no sheet is specified, the auto resize will be done 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 auto resize on a different sheet without changing the ACTIVE_SHEET property. When no range is specified or cells$ is an empty string "", the auto resize will be done for 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. Should be used after the cells are populated to adjust column widths to fit the existing data. Returns '1' if successful. Returns '0' if unable to locate the sheet or set the range. (The AutoFitColumns method was added in PxPlus 2025.)
AutoFitRows( ) AutoFitRows(cells$) AutoFitRows(cells$,sheet) AutoFitRows(cells,sheet_name$) | Auto resize the row heights based on cell content for a range of cells in a given sheet. When no sheet is specified, the auto resize will be done 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 auto resize on a different sheet without changing the ACTIVE_SHEET property. When no range is specified or cells$ is an empty string "", the auto resize will be done for 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. Should be used after the cells are populated to adjust row heights to fit the existing data. Returns '1' if successful. Returns '0' if unable to locate the sheet or set the range. (The AutoFitRows method was added in PxPlus 2025.)
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. Example: font$="Arial" sets only the font name. font$="Arial,12" sets the name and size. font$="Arial,12,Bold" sets the name and size and bolds the text in the range. #### Note: Valid font style values are 'Regular', 'Italic', 'Bold' and 'Bold Italic'. Returns '1' if successful. Returns '0' if unable to determine the sheet or range.
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" "NUMBER" "CURRENCY" "PERCENT" "DATE" "TIME" "DATE_TIME" "SCIENTIFIC" 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

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

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.

## Example: Testing the Google Sheets Object

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

## See Also

PxPlus Google Docs Object
PxPlus Google Drive Object

Google Workspace® is a registered trademark of Google LLC