README

Google Sheets Helper for Free Pascal

[!IMPORTANT] Using this library requires you to use a Service Account to access your Google Sheets spreadsheets. The Google Authentication section shows how to set this up.

This library is a Google Sheets helper for Free Pascal. It allows a developer to access a Google Sheet to:

1. Download values from the sheet to CSV (reading all rows)
2. Clear a sheet (deleting all rows)
3. Upload a CSV to a Google Sheet (updating/appending new rows)

This was written in Free Pascal with FPC version 3.2.3. I'm sure it can be used in Lazarus but I don't use that IDE so I can't guide you. It's written for Linux. If anyone wants to add OAuth or Windows support, please do so. I may get to these things and other features as I have time to do it.

At work we use Google Sheets to store data and control processes. This was written to allow my code to download sheets, clear them of values, and upload a new CSV to the sheet. You can do all of that with this library, without needing to struggle with Google Authentication or the Google Sheets V4 API.

Below you will find simple code that shows how easy it is to implement the major functions.

Core Operations

Before you run the following code you will need to setup a Google Service Account and create a service account key. You also need to decide how to store your environment variables and secrets (ie the Google service account key)

More details can be found here

Upload a CSV to Google Sheets

{$mode objfpc}{$H+}

uses
SysUtils, Classes, TGoogleSheetClient;

var
   JsonFile, SheetID, TabName :String;
   CSVData: TStringList;

begin

CSVData := TStringList.Create;
JsonFile := '/opt/auth/serviceaccount.json';
SheetID := '1234567890abcdefghijklmnop'; 
TabName := 'mydata';

try
    CSVData.LoadFromFile('mydata.csv');
    UploadCSVToGoogleSheet(SheetID, TabName, JsonFile, CSVData);

finally
    CSVData.Free;

end.

Clear all data from a Google Sheet

{$mode objfpc}{$H+}

uses
SysUtils, Classes, TGoogleSheetClient;

var
   JsonFile, SheetID, TabName :String;

begin

JsonFile := '/opt/auth/serviceaccount.json';
SheetID := '1234567890abcdefghijklmnop'; 
TabName := 'mydata';

ClearGoogleSheet(SheetID, TabName, JsonFile);

end.

Export Google Sheet to CSV

The CSV is created in the current directory, with the same name as the tabname. So, if your tabname is 'data' the CSV will be created as data.csv.

{$mode objfpc}{$H+}

uses
SysUtils, Classes, TGoogleSheetClient;

var
   JsonFile, SheetID, TabName :String;

begin

JsonFile := '/opt/auth/serviceaccount.json';
SheetID := '1234567890abcdefghijklmnop'; 
TabName := 'mydata';

DownloadGoogleSheet(SheetID, TabName, JsonFile);

end.

Authentication

[!IMPORTANT] You'll need Google Sheets API Service Account before you can use this library. It relies on the json file created in that process for authentication.

1. Access the Google APIs Console while logged into your Google account.
2. Create a new project and give it a name.
3. Click on ENABLE APIS AND SERVICES.
4. Find and enable the Google Sheet API.
5. Create new credentials to the Google Sheets API. Select "Other UI" from the dropdown and select Application Data. Then click on the "What credentials do I need?" button.
6. On the next screen, choose a name for your service account, assign it a role of Project->Editor, and click Continue. Be sure that JSON is checked for the format.
7. The credentials JSON file will be downloaded. Keep track of where it downloads to.
8. Go to your Google Sheet and click on the "share" button. Paste in the service account email address (it's in the JSON you downloaded) and set it up as an editor.
9. You're done. Now try one of the demo programs to make sure everything is set up correctly. You'll need the Sheet ID, the name of the tab where your data is, and the JSON file you downloaded earlier.

Here is how to find your sheet ID