API Reference

API Reference

Complete reference documentation for all methods available in the Google Sheets Helper library.

📋 Table of Contents

• Constructor
• Configuration Methods
• Authentication Methods
• Data Reading Methods
• Data Writing Methods
• Data Update Methods
• Worksheet Management Methods
• Formatting Methods
• Utility Methods

🏗️ Constructor

__construct(?string $credentialFilePath = null, ?string $tokenPath = null)

Creates a new Helper instance.

Parameters:

• $credentialFilePath (string|null) - Path to Google API credentials JSON file
• $tokenPath (string|null) - Path to authentication token JSON file

Returns: void

Example:

// Using environment variables (recommended)
putenv('credentialFilePath=path/to/credentials.json');
putenv('tokenPath=path/to/token.json');
$sheets = new Helper();

// Using direct file paths
$sheets = new Helper('path/to/credentials.json', 'path/to/token.json');

Throws: Exception if credential file doesn't exist

---

⚙️ Configuration Methods

setSpreadsheetId(?string $spreadsheetId)

Sets the current spreadsheet ID.

Parameters:

• $spreadsheetId (string|null) - The unique identifier of the spreadsheet

Returns: void

Example:

$sheets->setSpreadsheetId('1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms');

getSpreadsheetId()

Gets the current spreadsheet ID.

Parameters: None

Returns: string|null - The current spreadsheet ID

Example:

$id = $sheets->getSpreadsheetId();
echo "Current spreadsheet ID: {$id}\n";

setWorksheetName(?string $worksheetName)

Sets the current worksheet name.

Parameters:

• $worksheetName (string|null) - The exact name of the worksheet

Returns: void

Example:

$sheets->setWorksheetName('Sheet1');

getWorksheetName()

Gets the current worksheet name.

Parameters: None

Returns: string|null - The current worksheet name

Example:

$name = $sheets->getWorksheetName();
echo "Current worksheet: {$name}\n";

setSpreadsheetRange(?string $range)

Sets the current range to work with.

Parameters:

• $range (string|null) - Range in A1 notation (e.g., "A1:B10", "A:A", "1:1")

Returns: void

Example:

$sheets->setSpreadsheetRange('A1:Z1000');
$sheets->setSpreadsheetRange('A:A');  // Whole column A
$sheets->setSpreadsheetRange('1:1');  // Whole row 1

getSpreadsheetRange()

Gets the current range.

Parameters: None

Returns: string|null - The current range

Example:

$range = $sheets->getSpreadsheetRange();
echo "Current range: {$range}\n";

setValueInputOption(?string $valueInputOption)

Sets how values are interpreted when writing to sheets.

Parameters:

• $valueInputOption (string|null) - Either "RAW" or "USER_ENTERED"

Returns: void

Example:

$sheets->setValueInputOption('USER_ENTERED'); // Parse as if user typed
$sheets->setValueInputOption('RAW');          // Insert data as-is

getValueInputOption()

Gets the current value input option.

Parameters: None

Returns: string|null - The current value input option

Example:

$option = $sheets->getValueInputOption();
echo "Current input option: {$option}\n";

getService()

Gets the underlying Google Sheets service instance.

Parameters: None

Returns: Google_Service_Sheets - The Google Sheets service instance

Example:

$service = $sheets->getService();
// Access advanced Google Sheets API methods

---

🔐 Authentication Methods

firstAuth(?string $tokenPath = null)

Performs first-time authentication to generate a token file.

Parameters:

• $tokenPath (string|null) - Path where to save the token file

Returns: void

Example:

$sheets->firstAuth('path/to/token.json');

Throws: Exception if token path is not set

getClient()

Gets the authorized Google API client instance.

Parameters: None

Returns: Google_Client - The authorized Google client instance

Example:

$client = $sheets->getClient();
// Access advanced Google API methods

Throws: Exception if token file doesn't exist or is invalid

---

📖 Data Reading Methods

get()

Gets values from the specified range in the current worksheet.

Parameters: None

Returns: array - Array of values from the range

Example:

$sheets->setSpreadsheetRange('A1:C10');
$values = $sheets->get();

foreach ($values as $row) {
    echo implode(', ', $row) . "\n";
}

Throws: Exception if required properties are not set

getSingleCellValue(?string $cell)

Gets the value of a single cell.

Parameters:

• $cell (string|null) - Cell reference (e.g., "A1", "B2")

Returns: mixed|null - The cell value or null if not found

Example:

$value = $sheets->getSingleCellValue('B2');
echo "Value in B2: " . ($value ?: 'empty') . "\n";

Throws: Exception if required properties are not set

findCellByValue(?string $value, int $limit = 1)

Finds cells containing a specific value.

Parameters:

• $value (string|null) - The value to search for
• $limit (int) - Maximum number of results (0 for unlimited)

Returns: array|null - Array of found cells or null if not found

Example:

$sheets->setSpreadsheetRange('A1:Z100');
$result = $sheets->findCellByValue('John Doe');

if ($result) {
    echo "Found at: {$result['cell']} (row {$result['row']}, column {$result['column']})\n";
}

Throws: Exception if required properties are not set

---

✍️ Data Writing Methods

append(?array $rowsData = [])

Appends multiple rows to the current worksheet.

Parameters:

• $rowsData (array) - Multi-dimensional array of values to append

Returns: int - Number of rows updated

Example:

$sheets->setSpreadsheetRange('A1:C');
$rowsAdded = $sheets->append([
    ['John Doe', 'john@example.com', 'Developer'],
    ['Jane Smith', 'jane@example.com', 'Designer']
]);
echo "Added {$rowsAdded} rows\n";

Throws: Exception if required properties are not set

appendSingleRow(?array $row = [])

Appends a single row to the current worksheet.

Parameters:

• $row (array) - Array of values for the single row

Returns: object - Update response object

Example:

$sheets->setSpreadsheetRange('A1:C1');
$result = $sheets->appendSingleRow(['John Doe', 'john@example.com', 'Developer']);
echo "Row appended successfully\n";

Throws: Exception if required properties are not set

---

🔄 Data Update Methods

update(?array $newValues = [])

Updates a range of cells with new values.

Parameters:

• $newValues (array) - Multi-dimensional array of new values

Returns: object - Update response object

Example:

$sheets->setSpreadsheetRange('A1:F5');
$update = $sheets->update([
    ['Header1', 'Header2', 'Header3', 'Header4', 'Header5', 'Header6'],
    ['Data1', 'Data2', 'Data3', 'Data4', 'Data5', 'Data6']
]);
echo "Updated {$update->getUpdatedCells()} cells\n";

Throws: Exception if required properties are not set

updateSingleCell(?string $cell = null, ?string $value = null)

Updates a single cell with a new value.

Parameters:

• $cell (string|null) - Cell reference (e.g., "A1", "B2")
• $value (string|null) - New value to set

Returns: object - Update response object

Example:

$update = $sheets->updateSingleCell('B5', 'Updated Value');
if ($update->getUpdatedCells() >= 1) {
    echo "Cell updated successfully\n";
}

Throws: Exception if cell or value is not provided

---

📊 Worksheet Management Methods

getSpreadsheetWorksheets()

Gets all worksheets in the current spreadsheet.

Parameters: None

Returns: array - Array of worksheets with IDs and titles

Example:

$worksheets = $sheets->getSpreadsheetWorksheets();
foreach ($worksheets as $worksheet) {
    echo "Sheet: {$worksheet['title']} (ID: {$worksheet['id']})\n";
}

Throws: Exception if spreadsheet ID is not set

addWorksheet(?string $worksheetName, int $rowCount = 1000, int $columnCount = 26)

Creates a new worksheet in the current spreadsheet.

Parameters:

• $worksheetName (string|null) - Name of the new worksheet
• $rowCount (int) - Number of rows (default: 1000)
• $columnCount (int) - Number of columns (default: 26)

Returns: int - ID of the new worksheet

Example:

$newSheetId = $sheets->addWorksheet('New Sheet', 500, 20);
echo "Created worksheet with ID: {$newSheetId}\n";

Throws: Exception if spreadsheet ID is not set or worksheet name is empty

duplicateWorksheet(?string $newWorksheetName)

Duplicates the current worksheet with a new name.

Parameters:

• $newWorksheetName (string|null) - Name for the duplicated worksheet

Returns: int - ID of the new worksheet

Example:

$sheets->setWorksheetName('Sheet1');
$newSheetId = $sheets->duplicateWorksheet('Copy of Sheet1');
echo "Duplicated worksheet with ID: {$newSheetId}\n";

Throws: Exception if required properties are not set

renameWorksheet(?string $oldName, ?string $newName)

Renames a worksheet.

Parameters:

• $oldName (string|null) - Current worksheet name
• $newName (string|null) - New worksheet name

Returns: bool - True if renamed successfully

Example:

$renamed = $sheets->renameWorksheet('Old Name', 'New Name');
if ($renamed) {
    echo "Worksheet renamed successfully\n";
}

Throws: Exception if spreadsheet ID is not set or names are empty

deleteWorksheet(?string $worksheetName)

Deletes a worksheet by name.

Parameters:

• $worksheetName (string|null) - Name of the worksheet to delete

Returns: bool - True if deleted successfully

Example:

$deleted = $sheets->deleteWorksheet('Sheet to Delete');
if ($deleted) {
    echo "Worksheet deleted successfully\n";
}

Throws: Exception if spreadsheet ID is not set or worksheet name is empty

---

🎨 Formatting Methods

colorRange(?array $rgb)

Changes the background color of a range.

Parameters:

• $rgb (array) - RGB color values [red, green, blue, alpha]

Returns: void

Example:

$sheets->setSpreadsheetRange('A1:Z10');
$sheets->colorRange([142, 68, 173]); // Purple
echo "Background color applied\n";

Throws: Exception if required properties are not set or RGB values are invalid

clearRange()

Clears all values in the specified range.

Parameters: None

Returns: bool - True if cleared successfully

Example:

$sheets->setSpreadsheetRange('A1:Z100');
$cleared = $sheets->clearRange();
if ($cleared) {
    echo "Range cleared successfully\n";
}

Throws: Exception if required properties are not set

---

🛠️ Utility Methods

create(?string $title)

Creates a new spreadsheet.

Parameters:

• $title (string|null) - Title for the new spreadsheet

Returns: string - ID of the new spreadsheet

Example:

$newSpreadsheetId = $sheets->create('My New Spreadsheet');
echo "Created spreadsheet with ID: {$newSpreadsheetId}\n";

Throws: Exception if title is empty

getColumnLettersIndex(?string $letters = null)

Calculates the column index from column letters.

Parameters:

• $letters (string|null) - Column letters (e.g., "A", "B", "AA", "AZ")

Returns: int - Column index (1-based)

Example:

$index = Helper::getColumnLettersIndex('AZ'); // Returns 52
$index = Helper::getColumnLettersIndex('AA'); // Returns 27
echo "Column AZ is at index: {$index}\n";

Throws: None (static method)

---

📝 Method Summary

Category	Method	Description
Constructor	__construct()	Create new Helper instance
Configuration	setSpreadsheetId()	Set spreadsheet ID
Configuration	getSpreadsheetId()	Get spreadsheet ID
Configuration	setWorksheetName()	Set worksheet name
Configuration	getWorksheetName()	Get worksheet name
Configuration	setSpreadsheetRange()	Set working range
Configuration	getSpreadsheetRange()	Get working range
Configuration	setValueInputOption()	Set value input option
Configuration	getValueInputOption()	Get value input option
Configuration	getService()	Get Google service instance
Authentication	firstAuth()	First-time authentication
Authentication	getClient()	Get Google client instance
Reading	get()	Get range values
Reading	getSingleCellValue()	Get single cell value
Reading	findCellByValue()	Search for values
Writing	append()	Append multiple rows
Writing	appendSingleRow()	Append single row
Updating	update()	Update range
Updating	updateSingleCell()	Update single cell
Worksheets	getSpreadsheetWorksheets()	List worksheets
Worksheets	addWorksheet()	Create worksheet
Worksheets	duplicateWorksheet()	Duplicate worksheet
Worksheets	renameWorksheet()	Rename worksheet
Worksheets	deleteWorksheet()	Delete worksheet
Formatting	colorRange()	Change background color
Formatting	clearRange()	Clear range values
Utility	create()	Create spreadsheet
Utility	getColumnLettersIndex()	Calculate column index

---

🚨 Important Notes

Required Properties

Most methods require these properties to be set first:

• spreadsheetId - via setSpreadsheetId()
• worksheetName - via setWorksheetName()
• range - via setSpreadsheetRange()

Error Handling

All methods throw Exception when:

• Required properties are not set
• Google API calls fail
• Invalid parameters are provided
• Authentication issues occur

Return Types

• Data methods return arrays or single values
• Update methods return response objects with metadata
• Boolean methods return true/false for success/failure
• ID methods return integer identifiers

---

Need more details? Check the Examples page for practical usage examples!