In many cases, storing data with easy access is essential. For example, you may need to keep records of employees, scientific experiment results, and more.
The standard solution is to use a database. But, in some scenarios, it is much more convenient and efficient to store data in Google Sheets. Currently, anyone can create a free Gmail account, which also grants access to various additional Google services. For example, one such service is Google Drive, which provides each user with 15 GB of free cloud storage.
You can also use Google Sheets to create and edit spreadsheets.
Learn how to send an email via Embarcadero Delphi FMX app by using Gmail API.
In this article, we will show how to save data to a Google spreadsheet and then retrieve it using our Embarcadero Delphi FMX application. The spreadsheet containing our data will be stored in a folder on Google Drive.
Setting Up the Google Sheet
To get started, we first need to create a folder on Google Drive, where we will store the spreadsheet file.
In the SoftacomGoogleDrive folder (in our case), we will create a Google spreadsheet.
Our spreadsheet will include the following columns:
- Date – stores the date and time when the record was saved
- ID – stores the record number in the spreadsheet
- Name – stores the person’s first name
- Surname – stores the person’s last name
- Profession – stores the person’s profession
Creating the JavaScript Scripts
To add and retrieve records from the spreadsheet using our Embarcadero Delphi FMX application, we need to create two JavaScript scripts: one for adding data and another for reading data from the spreadsheet.
Let’s start by creating the script for adding data.
We will name our script file for adding data PostToGoogleSheet. In the string variable ss, we need to pass the URL address of our Google spreadsheet. We also assign the name of the Items tab in the spreadsheet to the sheet variable.
Now, let’s examine the body of the doPost function, which is responsible for saving data to the spreadsheet.
Next, create the following variables: date, id, itemName, itemSurname, and itemProfession, which store the record’s timestamps, record number, first name, last name, and profession, respectively.
The appendRow method adds a new record to the spreadsheet.
To transfer parameters (first name, last name, and profession) from our Embarcadero Delphi FMX application to a Google Sheet table, we need to deploy the PostToGoogleSheet script as a web application. To do this, follow the steps:
- Click Deploy.
2. Select New Deployment
3. Select the deployment type. In our case, choose Web app.
4. Next, you need to enter a description of the script. For example, Postdata.
5. Specify the account under which the script will be executed (in this case, softacomtest2022@gmail.com).
6. Define access permissions (in our case, everyone can access it).
7. Click Deploy.
8. Click Authorize access to grant necessary permissions.
Select your Google account.
Next, click Advanced.
Click Go to…
Click Allow.
Copy the URL of the deployed web application (script) and save it for future use in our Embarcadero Delphi FMX application. Click Done.
The complete script code to add data is below.
| 123456789101112 | var ss=SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/11Suz_KQytqasX5Mkd_Bv5jd5YTWIbhVBATEZViz8Lfo/edit?gid=0#gid=0");
var sheet=ss.getSheetByName("Items");
function doPost(e)
{
var date=new Date();
var id=sheet.getLastRow();
var itemName=e.parameter.Name;
var itemSurname=e.parameter.Surname;
var itemProfession=e.parameter.Profession;
sheet.appendRow([date,id,itemName,itemSurname,itemProfession]);
return ContentService.createTextOutput("Success :)))").setMimeType(ContentService.MimeType.TEXT);
}
|
Similarly, we create and deploy a script to retrieve data from the spreadsheet and send it to our Embarcadero Delphi FMX application. We will name this script GetDataFromGoogleSheet.
Connecting to the Google spreadsheet by URL follows the same process as in the script for adding data. Deploying the script as a web application is also done in the same way. Just like with the script for writing data, we must save the deployed web application URL for reading data. This will be used later in our Embarcadero Delphi FMX application.
Now, let’s examine the getData function, which retrieves data from the spreadsheet. This function reads all records from the table and stores them in the dataArray array. The records are then converted into JSON format for easy processing.
The getData function is called within the doGet method, which retrieves the table’s data and returns it in JSON format.
We will load this JSON response into our Embarcadero Delphi FMX application.
The complete script code for retrieving data from Google Sheets is shown below.
| 12345678910111213141516171819202122232425 | var ss=SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/11Suz_KQytqasX5Mkd_Bv5jd5YTWIbhVBATEZViz8Lfo/edit?gid=0#gid=0");
var sheet=ss.getSheetByName("Items");
function doGet(e) {
return getData(sheet);
}
function getData(sheet)
{
var jo={};
var dataArray=[];
var rows=sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn()).getValues();
for(var i=0,l=rows.length;i<l;i++)
{
var dataRow=rows[i];
var record={};
record["data"]=dataRow[0];
record["id"]=dataRow[1];
record["name"]=dataRow[2];
record["surname"]=dataRow[3];
record["profession"]=dataRow[4];
dataArray.push(record);
}
jo.user = dataArray;
var result = JSON.stringify(jo);
return ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.JSON);
}
|
Integrating with Embarcadero Delphi FMX
Now, let’s look at how our Embarcadero Delphi FMX application will interact with Google Sheets to save and retrieve records.
To send POST and GET requests, we will use the TNetHTTPClient component, which allows our application to communicate with the deployed web scripts.
We use the TEdit component to enter the parameters that will be sent to the table.
To save records in the table and retrieve them later, we use the TButton component.
To display the retrieved records in JSON format, we use the TMemo component.
We use the string constants MacrosPostURL and MacrosGetURL to store the URLs of the previously deployed web applications.
All the logic for transferring and saving data to the spreadsheet is implemented in the onClick handler of the “Post to GoogleSheet” button. We will send three parameters: first name, last name, and profession.
The InputData object (TMultipartFormData class) contains these parameters for saving them in the spreadsheet (Name, Surname, Profession).
The MemoryStream object (TMemoryStream class) stores the response from the web application, indicating whether the data transfer and saving to the Google spreadsheet was successful.
The POST request to send our parameters to the spreadsheet is executed using the NetHTTPClient1.Post method.
To prevent the application interface from freezing during the POST request, we use TTask.Run.
The result of sending and saving data to the table is displayed in the main thread using TThread.Synchronize.
To display the result of the data transfer to the table, the Memo1.Lines.LoadFromStream method is used.
The full code for the “Post to GoogleSheet” button handler is shown below.
| 1234567891011121314151617181920212223242526272829 | procedure TForm1.Button1Click(Sender: TObject);
var
InputData: TMultipartFormData;
MemoryStream: TMemoryStream;
begin
TTask.Run(procedure
begin
InputData := nil;
MemoryStream := nil;
try
InputData := TMultipartFormData.Create;
InputData.AddField('Name', EditName.Text);
InputData.AddField('Surname', EditSurname.Text);
InputData.AddField('Profession', EditProfession.Text);
MemoryStream := TMemoryStream.Create;
NetHTTPClient1.Post(MacrosPostURL,
InputData, MemoryStream);
TThread.Synchronize(nil,
procedure
begin
Memo1.Lines.LoadFromStream(MemoryStream);
TabControl1.GotoVisibleTab(1)
end);
finally
MemoryStream.Free;
InputData.Free;
end;
end);
end;
|
To present the JSON response from the web application in a readable format, we use the FormatJSON function.
All the logic for loading data from the table is implemented in the onClick handler of the “Get from GoogleSheet” button.
The ResponseStream object (TStringStream class) stores the response from the web application containing the table data in JSON format.
The GET request to retrieve data from the table is executed using the NetHttpClient1.Get method.
The received JSON response is displayed in TMemo using:
Memo1.Text := FormatJSON(ResponseStream.DataString).
The full code for the “Get from GoogleSheet” button handler is shown below.
| 123456789101112131415161718192021 | procedure TForm1.Button2Click(Sender: TObject);
var ResponseStream: TStringStream;
begin
Memo1.Lines.Clear;
TTask.Run(
procedure
begin
ResponseStream := TStringStream.Create;
try
NetHttpClient1.Get(MacrosGetURL, ResponseStream);
TThread.Synchronize(nil,
procedure
begin
Memo1.Text := FormatJSON(ResponseStream.DataString);
TabControl1.GotoVisibleTab(1)
end);
finally
ResponseStream.Free;
end;
end);
end;
|
The full code of the main form module is shown below.
| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110 | unit Main;
interface
uses
System.SysUtils, System.Types, System.UITypes, System.Classes, System.Variants,
FMX.Types, FMX.Controls, FMX.Forms, FMX.Graphics, FMX.Dialogs, FMX.Effects,
FMX.StdCtrls, FMX.Controls.Presentation, System.Net.URLClient,
System.Net.HttpClient, System.Net.HttpClientComponent, System.Rtti,
System.Bindings.Outputs, FMX.Edit, FMX.Layouts, FMX.Memo, FMX.TabControl,
FMX.Memo.Types, Json, FMX.Objects, FMX.ScrollBox;
const
MacrosPostURL = 'https://script.google.com/macros/s/AKfycbySpO3VHGEvEb1MSFI-ydIg1Uh9XnaniTCtXds-uDhSZFTmHa8xUdKdzKF3I9wAVU4_BA/exec';
MacrosGetURL = 'https://script.google.com/macros/s/AKfycbxquX9c6rZcp89vlqHHcr_d75gmEijBw-0wfMvFxtSkp9ik2_QOQ2ubbH6_bo3hdZC7/exec';
type
TForm1 = class(TForm)
ToolBar1: TToolBar;
Label1: TLabel;
Button1: TButton;
TabControl1: TTabControl;
TabItem1: TTabItem;
TabItem2: TTabItem;
Memo1: TMemo;
NetHTTPClient1: TNetHTTPClient;
Label4: TLabel;
EditName: TEdit;
Label2: TLabel;
EditProfession: TEdit;
Label5: TLabel;
EditSurname: TEdit;
Button2: TButton;
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
private
{ Private declarations }
FMacrosPostURL: string;
FMacrosGetURL: string;
public
{ Public declarations }
function FormatJSON(const JSON: string): string;
end;
var
Form1: TForm1;
implementation
{$R *.fmx}
uses
System.Threading, System.Net.Mime, System.IOUtils;
function TForm1.FormatJSON(const JSON: string): string;
var
JsonObject: TJsonObject;
begin
JsonObject := TJsonObject.ParseJSONValue(JSON) as TJsonObject;
try
if Assigned(JsonObject) then
Result := JsonObject.Format()
else
Result := JSON;
finally
JsonObject.Free;
end;
end;
procedure TForm1.Button1Click(Sender: TObject);
var
InputData: TMultipartFormData;
MemoryStream: TMemoryStream;
begin
TTask.Run(procedure
begin
InputData := nil;
MemoryStream := nil;
try
InputData := TMultipartFormData.Create;
InputData.AddField('Name', EditName.Text);
InputData.AddField('Surname', EditSurname.Text);
InputData.AddField('Profession', EditProfession.Text);
MemoryStream := TMemoryStream.Create;
NetHTTPClient1.Post(MacrosPostURL,
InputData, MemoryStream);
TThread.Synchronize(nil,
procedure
begin
Memo1.Lines.LoadFromStream(MemoryStream);
TabControl1.GotoVisibleTab(1)
end);
finally
MemoryStream.Free;
InputData.Free;
end;
end);
end;
procedure TForm1.Button2Click(Sender: TObject);
var ResponseStream: TStringStream;
begin
Memo1.Lines.Clear;
TTask.Run(
procedure
begin
ResponseStream := TStringStream.Create;
try
NetHttpClient1.Get(MacrosGetURL, ResponseStream);
TThread.Synchronize(nil,
procedure
begin
Memo1.Text := FormatJSON(ResponseStream.DataString);
TabControl1.GotoVisibleTab(1)
end);
finally
ResponseStream.Free;
end;
end);
end;
end.
|
Performance Testing
Let’s test the performance of our Embarcadero Delphi FMX application. Data transfer and writing:
Reading data from the table:
