Tutorials

Tutorials

Reading, Writing, and Formatting Google Sheets

GPAL.GoogleSheets gives you a fluent client over the Google Sheets API: create spreadsheets and sheets, write and read grids of data, append/prepend/insert at specific cells, apply formatting, run sum and count calculations, and save results to a local file.

Complete Program

Here's the whole workflow, start to finish. Each piece is broken down and explained below.

using System.Collections.Generic;

using GenerallyPositive;

using static GenerallyPositive.Enums;

ICredentials credentials = GPAL.CredentialsFor(CredentialServiceType.Google);

credentials

.WithUsername("me@example.com")

.WithPassword("my-password");

var googleSheets = GPAL.GoogleSheets.ToGPALObject();

// Create and configure a spreadsheet

googleSheets.WithCredentials(credentials)

.WithSpreadsheet("TestSpreadsheet")

.SetSpreadsheetTitle("Test Spreadsheet Title")

.WithSheet("TestSheet")

.SetSheetName("RenamedTestSheet")

.WithSheet("RenamedTestSheet");

// Write a grid of data, then read it back

var data = new List<List<string>>

{

new List<string> { "Name", "Age", "City" },

new List<string> { "John", "30", "New York" },

new List<string> { "Jane", "25", "Los Angeles" }

} as IGPALGrid<string>;

googleSheets.WithCredentials(credentials)

.WithSpreadsheet("TestSpreadsheet")

.WithSheet("RenamedTestSheet")

.WithData(data)

.WithWriteRange("A1:C3")

.WriteToSheet("RenamedTestSheet");

IGPALGrid<string> readData;

googleSheets.WithReadRange("A1:C3")

.SaveTo(out readData);

// Format and calculate

googleSheets

.WithFormatType(FormatType.Bold)

.WithFormatValue(true)

.FormatRange("A1:C1")

.WithFormatType(FormatType.NumberFormat)

.WithNumberFormatValue(NumberFormatType.CURRENCY)

.FormatRange("B2:B3");

string sumResult;

googleSheets.WithReadRange("B2:B3")

.CalculateSum(out sumResult);

// Save to a local file

GPALFile file = GPAL.FileFor("output.csv");

googleSheets.WithReadRange("A1:C3")

.SaveTo(file);

Credentials and the GoogleSheets Object

Like other GPAL Google integrations, you get credentials via GPAL.CredentialsFor(CredentialServiceType.Google) and supply username/password (or OAuth client details). GPAL.GoogleSheets.ToGPALObject() returns the reusable client - WithCredentials attaches your identity to subsequent calls.

ICredentials credentials = GPAL.CredentialsFor(CredentialServiceType.Google);

credentials

.WithUsername("me@example.com")

.WithPassword("my-password");

var googleSheets = GPAL.GoogleSheets.ToGPALObject();

Create a Spreadsheet and Rename a Sheet

WithSpreadsheet picks (or creates) a spreadsheet by name, and SetSpreadsheetTitle sets its display title. WithSheet selects a tab within the spreadsheet, and SetSheetName renames it. Calling WithSheet again with the new name switches the active sheet to it.

googleSheets.WithCredentials(credentials)

.WithSpreadsheet("TestSpreadsheet")

.SetSpreadsheetTitle("Test Spreadsheet Title")

.WithSheet("TestSheet")

.SetSheetName("RenamedTestSheet")

.WithSheet("RenamedTestSheet");

Write and Read a Grid

WithData attaches an IGPALGrid<string> of rows, WithWriteRange picks the target cell range, and WriteToSheet performs the write. Reading is the mirror image: WithReadRange picks the range and SaveTo(out grid) pulls the values back into an IGPALGrid<string>.

googleSheets.WithCredentials(credentials)

.WithSpreadsheet("TestSpreadsheet")

.WithSheet("RenamedTestSheet")

.WithData(data)

.WithWriteRange("A1:C3")

.WriteToSheet("RenamedTestSheet");

IGPALGrid<string> readData;

googleSheets.WithReadRange("A1:C3")

.SaveTo(out readData);

Append, Prepend, and Insert

Beyond a full-range write, you can target single cells. AppendToCell and PrependToCell add data before or after existing content, and InsertAtCell drops data at a specific position - WithInsertSeparator and WithInsertPosition control how multiple values are joined and where they land.

var appendData = new List<List<string>> { new List<string> { "Extra", "Data", "Here" } } as IGPALGrid<string>;

googleSheets.WithCredentials(credentials)

.WithSpreadsheet("TestSpreadsheet")

.WithSheet("RenamedTestSheet")

.WithData(appendData)

.AppendToCell("A4")

.WithInsertSeparator(", ")

.WithInsertPosition(0)

.InsertAtCell("C4")

.PrependToCell("B4");

Formatting, Calculations, and Saving to a File

FormatRange applies whatever WithFormatType/WithFormatValue (or WithNumberFormatValue) describe - bold text, currency formatting, and so on - to a cell range. CalculateSum and CalculateCount run aggregate formulas over the active read range. Finally, SaveTo(GPALFile) writes the contents of a read range straight to a local file using GPAL's file abstraction.

googleSheets

.WithFormatType(FormatType.Bold)

.WithFormatValue(true)

.FormatRange("A1:C1")

.WithFormatType(FormatType.NumberFormat)

.WithNumberFormatValue(NumberFormatType.CURRENCY)

.FormatRange("B2:B3");

string sumResult;

googleSheets.WithReadRange("B2:B3")

.CalculateSum(out sumResult);

GPALFile file = GPAL.FileFor("output.csv");

googleSheets.WithReadRange("A1:C3")

.SaveTo(file);

TIP

GPAL.GoogleSheets also supports DeleteSheet, ListSheets, and even uploading and deploying Apps Script with triggers (UploadScript, DeployScriptAsWebApp, CreateTrigger) for workflows that need automation inside the spreadsheet itself.

On this page

Tutorials
Reading, Writing, and Formatting Google Sheets
  • Complete Program
  • Credentials and the GoogleSheets Object
  • Create a Spreadsheet and Rename a Sheet
  • Write and Read a Grid
  • Append, Prepend, and Insert
  • Formatting, Calculations, and Saving to a File