Recent

Author Topic: How to use googleapi for google spreadsheet?  (Read 1849 times)

jus

  • New Member
  • *
  • Posts: 19
How to use googleapi for google spreadsheet?
« on: December 02, 2019, 05:36:21 pm »
Hi,

  i want to use google spreadsheet in lazarus. It seems that there is no Lazarus component for spreadsheet available in the lazarus googleapi package. But there exists a \fpcsrc\packages\googleapi\src\googlesheets.pp file. Does someone know how to connect to the google spreadsheet?

jus

maurobio

  • Full Member
  • ***
  • Posts: 102
Re: How to use googleapi for google spreadsheet?
« Reply #1 on: December 02, 2019, 10:42:41 pm »
Hi,

I have not an answer, but I am posting this reply just to emphasize that I also have this very same question and would surely appreciate any help on how to use Google Spreadsheets with Lazarus (probably in association with the powerful fpSpreadsheet library).

Cheers,
UCSD Pascal / Burroughs 6700 / Master Control Program
Lazarus 1.9.3/2.0.4 - FPC 3.0.4 on GNU/Linux Mint 19 ("Tessa"), Windows XP SP3, Windows 7 Professional, Windows 10 Home

wp

  • Hero Member
  • *****
  • Posts: 6471
Re: How to use googleapi for google spreadsheet?
« Reply #2 on: December 03, 2019, 01:33:21 am »
I had once played with the Google spreadsheets a bit, but found out that is is not possible to copy them to local files. Because fpspreadsheet development requires local files for reverse engineering of the file structure I decided to skip them for fpspreadsheet.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

jus

  • New Member
  • *
  • Posts: 19
Re: How to use googleapi for google spreadsheet?
« Reply #3 on: December 05, 2019, 02:04:01 am »
First of all it seems that the calendar sample project (\lazarus\components\googleapis\demo\calendar) doesn't work in the trunk version Laz(2.1.0)+FPC(3.3.1) created with fpcupdeluxe anymore. After installing the googleapi and the synapse component the calendar sample compiles with success. But if I run the app lazarus stops in application.inc at the end of the "procedure TApplication.ProcessAsyncCallQueue" method. With the stable version Laz(2.0.2)+FPC(3.0.4) created with fpcupdeluxe it seems to run.
So used the stable Laz(2.0.2)+FPC(3.0.4) version and the calendar demo as a starting point for google spreadsheet.

Code: Pascal  [Select]
  1. { TMainForm }
  2.  
  3. procedure TMainForm.FormCreate(Sender: TObject);
  4. begin
  5.   // Register resources.
  6.   TSheetsAPI.RegisterAPIResources;
  7.   // Set up google client.
  8.   FClient:=TGoogleClient.Create(Self);
  9. {$IFDEF USESYNAPSE}
  10.   FClient.WebClient:=TSynapseWebClient.Create(Self);
  11.   Memo1.Lines.Add('$IFDEF USESYNAPSE');
  12. {$ELSE}
  13.   FClient.WebClient:=TFPHTTPWebClient.Create(Self);
  14.   showmessage('Falsch!');
  15. {$ENDIF}
  16.   FClient.WebClient.RequestSigner:=FClient.AuthHandler;
  17.   FClient.WebClient.LogFile:='requests.log';
  18.   FClient.AuthHandler.WebClient:=FClient.WebClient;
  19.   FClient.AuthHandler.Config.AccessType:=atOffLine;
  20.   // We want to enter a code.
  21.   FClient.OnUserConsent:=@DoUserConsent;
  22.  
  23.   FSheetsAPI:=TSheetsAPI.Create(Self);
  24.   FSheetsAPI.GoogleClient:=FClient;
  25.  
  26.   // Load configuration
  27.   LoadAuthConfig;
  28. end;  
  29.  
  30. procedure TMainForm.BFetchCalendarsClick(Sender: TObject);
  31. var
  32.   Resource : TSpreadsheetsValuesResource;
  33.   EN : String;
  34.   i:integer;
  35.   RangeData: TValueRange;
  36. begin
  37.   SaveRefreshToken;
  38.   RangeData:=Nil;
  39.   Resource:=Nil;
  40.   Resource := FSheetsAPI.CreateSpreadsheetsValuesResource;
  41.   try
  42.     if assigned(Resource) then
  43.     begin
  44.       Memo1.Lines.Add('resources assigned');
  45.       RangeData:= Resource.Get('****your spread sheet id****','Tabellenblatt1!A1:B3');
  46.     end;
  47.   finally
  48.     FreeAndNil(Resource);
  49.   end;
  50.  
  51. end;
  52.  

The good news is that google really send the requested cells (in my case above A1:B3) back as a json string. The json string from google in my case looks like:
Code: Text  [Select]
  1. {
  2.   "range": "Tabellenblatt1!A1:B3",
  3.   "majorDimension": "ROWS",
  4.   "values": [
  5.     [
  6.       "quantity",
  7.       "product"
  8.     ],
  9.     [
  10.       "1",
  11.       "product 1"
  12.     ],
  13.     [
  14.       "2",
  15.       "product 2"
  16.     ]
  17.   ]
  18. }

But the problem is that the json values cannot be saved to the TValueRange object. An exception is raised. After some debugging it seems that the "procedure TBaseObject.SetArrayElements" method in restbase.pp cannot handle the tkdynarray type. I guess the TBaseObject.SetArrayElements can only handle 1 dimensional arrays but not multidimensional arrays. My problem currently is that I didn't really understand how the TValueRange can hold a multidimensional array. How can it be filled with data. Could someone help?

« Last Edit: December 05, 2019, 02:27:33 am by jus »