B4A Library GSheet Library (integrate google sheets into your B4X apps easily)

Gsheet

Author:
Fernando Arevalo
Version: 1.0

Functions:
  • Process_Globals As String
B4A, B4I
  • Initialize (ClientId As String)
B4J
  • Initialize(ClientId As String,ClientSecret As String)
B4X
  • SpreadsheetsId As String
    The ID of your document. This is the big long aplha-numeric code in the middle of the document's URL.
  • Sheets_name As String
    Name of the sheet we are going to work on
  • Sub delete(id As Int)
    id: row number to delete
  • Updatesheet(id As String, l As List)
    id: row number to Update and l: list of new items
  • UpdateSheet_range(range As String, value As String)
    range: position of the value to be updated example: "A2". value: new value
  • insert_list(l As List)
    l: list of new items, these items will be added in the next empty row( if a list is created with the total or partial elements of a database it will be added as a single query immediately to your sheet)


Gsheetplus

Author:
Fernando Arevalo
Version: 1.0

Functions:
  • Process_Globals As String
B4A, B4I
  • Initialize (ClientId As String)
B4J
  • Initialize(ClientId As String,ClientSecret As String)
B4X
  • SpreadsheetsId As String
    The ID of your document. This is the big long aplha-numeric code in the middle of the document's URL.
  • Sheets_name As String
    Name of the sheet we are going to work on
  • Sub delete(id As Int)
    id: row number to delete
  • Updatesheet(id As String, l As List)
    id: row number to Update and l: list of new items
  • UpdateSheet_range(range As String, value As String)
    range: position of the value to be updated example: "A2". value: new value
  • insert_list(l As List)
    l: list of new items, these items will be added in the next empty row( if a list is created with the total or partial elements of a database it will be added as a single query immediately to your sheet)
  • copytodrive(title As String,sheetcopy As String)
    creates a copy in the drive of the account with which you enter a selected sheet you must define the name of the new spreadsheet (title) and the name of the sheet to copy (sheetcopy)
  • createtitle( l As List, sheetsname As String, fontsize As Int,bold As Boolean)
    l: list of titles, sheetsname: name of the sheet where the titles will be inserted
  • create_spreadsheets(title1 As String)
    title: name of the new spreadsheet
  • create_sheets(name_sheets As String,SpreadsheetId As String)
    name_sheets: name of the new sheet, SpreadsheetId:The ID of your document
  • deletesheet(sheetsname As String )
    name_sheets: name of the sheet for delete into spreadsheet
  • newspreadsheetsid As String
    allows to retrieve the id after creating a new spreadsheet or after backing up one in drive to save it in a database or variable it is recommended to put a wait of 2s before calling the value of this variable
  • newnamesheet As String
    allows you to retrieve the name of the new sheet created to save it in a database or variable it is recommended to wait for 2s before calling the value of this variable



NOTE:

This library is not free, because, it took a lot of time and gray hair to create all the methods and learn how the api works.
Please write GSheets or GSheetplus in the order description, thanks.



Thanks for your understanding. :)

This library depends the Google OAuth2 class:
follow the tutorial in this topic to create your project in google console developer, enable google dive and google sheets apis, make sure to add your package name to your project, and create an oauth screen.

Add this in your manifest:

manifest:
AddActivityText(Main,
  <intent-filter>
  <action android:name="android.intent.action.VIEW" />
  <category android:name="android.intent.category.DEFAULT" />
  <category android:name="android.intent.category.BROWSABLE" />
  <data android:scheme="$PACKAGE$" />
  </intent-filter>
   )

Read example for v4 api:
Read sheets:
Sub GetSheet
table1.ClearAll
    Dim j As HttpJob
    j.Initialize("", Me)
    j.Download("https://sheets.googleapis.com/v4/spreadsheets/17YACjfqxKHDCXM_N7Jnq9BC_KzzkEgXLKzHDwaIM5Zs/values/Example!A2:Z?key=xxxxxxxxxxxxxxxxxxxxxxxxx")
    Wait For (j) JobDone(j As HttpJob)
    If j.Success Then
        Dim parser As JSONParser
        parser.Initialize(j.GetString.Trim)
        Dim root As Map = parser.NextObject
        'Dim majorDimension As String = root.Get("majorDimension")
        Dim values As List = root.Get("values")
 
        For Each colvalues As List In values
 
  Dim id1 As Int = colvalues.Get("4")
            Dim create As String = colvalues.Get("3")
            Dim Telephone1 As Int = colvalues.Get("0")
            Dim Age1 As Int = colvalues.Get("2")
            Dim Name1 As String = colvalues.Get("1")
            If colvalues.Size > 0 Then
             table1.AddRow(Array As String(id1,Telephone1,Name1,Age1,create))
            End If
 
        Next
        Dim range As String = root.Get("range")
        Log(range)
    End If

 

    j.Release
End Sub

Read example with Gsheets
ReadGsheet Gsheets::
Sub GetSheet

 
    Table1..ClearAll
    s.GetSheet(Me)
    Wait For get_result(x As List)
 
    For Each col As Map In x
 
        Table1.AddRow(Array As String(col.Get("1"),col.Get("Téléphone"),col.Get("Name"),col.Get("Age"),col.Get("created")))
 
    Next
 
End Sub

Example connect:

Example connect:
#Region  Activity Attributes
    #FullScreen: false
    #IncludeTitle: FALSE
#End Region

Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.

End Sub

Sub Globals
    'These global variables will be redeclared each time the activity is created.
    'These variables can only be accessed from this module.

    Dim s As GSheets
    Private conect1 As Button
End Sub

Sub Activity_Create(FirstTime As Boolean)
    'Do not forget to load the layout file created with the visual designer. For example:
    s.Initialize("xxxxxxxxxxxxxxxx.apps.googleusercontent.com")'your Clientid google developers console, activate google drive api and google sheets api
    s.Sheets_name = "Example"
    s.SpreadsheetsId = "1FHx-a_a4afTQ3L5hXJIOttCVaJNngO3113E-qqFOjbk"
    Activity.LoadLayout("sheets1")
 
End Sub

'Connect
Sub connect1_Click
    s.connect
End Sub

Sub Activity_Resume

s.oauth2.CallFromResume(Activity.GetStartingIntent)
 
End Sub


Advantages:
  • It is not linked to paying an external api
  • the google sheet api is totally free
  • You can integrate multiple apps into a single project in the google console
  • Easy to use and integrate
Download page:

Download Gsheetsplus:


Download Gsheets:

gsheets2json.xyz


Example B4A:

Important in the verification screen of your project in google cloud activate this permission:
permiso.png

Important when generating a key for android activate this:
importante.png


Example google sheet:
Example api
 

Attachments

  • ExampleB4J.zip
    3.8 KB · Views: 145
Last edited:

fernando1987

Active Member
Licensed User
Dear, check the page and it is working normally, please try to delete the cookies and reload the page, enter and let me know if you still have problems
 

Attachments

  • Screenshot_20221122-061338_Chrome.jpg
    Screenshot_20221122-061338_Chrome.jpg
    268.6 KB · Views: 135

fernando1987

Active Member
Licensed User
No, but inside the library, being b4x, there are the modules with the code for the api calls, it includes some json responses... with that and some free time, maybe it could be implemented... sorry, dear friend, maybe in the future Prepare something... by the way, I am a great admirer of your work and I would like to congratulate you on having achieved great things.
 

asales

Expert
Licensed User
Longtime User
Dear, check the page and it is working normally, please try to delete the cookies and reload the page, enter and let me know if you still have problems
[ edit: solved - see #27 ]
Same problem.
Tested with 3 different browsers (Firefox, Chrome and Edge) and a new installation of Firefox:
1669322516771.png
 
Last edited:

asales

Expert
Licensed User
Longtime User
Same problem.
Tested with 3 different browsers (Firefox, Chrome and Edge) and a new installation of Firefox:
Problem solved!
I made the registration (using Edge) and after the checkout apply the coupon. If I try to apply the coupon without login, the error is raised.
 

fernando1987

Active Member
Licensed User
All the source codes and libraries of the store until the last day of the month of February will be at a 30% discount with the following coupon:

STORECODE30​

 

fernando1987

Active Member
Licensed User
*Version 4.0 available


* get method added
Get sheet:
    sub getsheets
    s.SpreadsheetsId = "xxxxxxxxxxxxxx"
    s.Sheets_name =  "hoja1"
    s.GetSheet(Me)
    Wait For get_result(x As List)
    
    For Each col As Map In x
        
        TableView1.Items.Add(Array As String(col.Get("1"),col.Get("Téléphone"),col.Get("Name"),col.Get("Age"),col.Get("created")))
        
    Next
    
    end sub

* Correction and improvement of the code

For those who have already acquired earlier version, enter the store with their credentials in their user panel in the recent orders part, they are placed in Gsheets see order and that's it, they can download it again from the download option
 

fernando1987

Active Member
Licensed User
Version 5.0 now available!
- Extensive code improvements and optimizations have been made.
- Introducing a new module called Gdrive in Gesheetsplus, allowing you to seamlessly work with files in Google Drive. You can now effortlessly search, create folders, delete files, upload files, and download files. Special thanks to @fredo, @Erel and @mw71, for their valuable contributions with b4x community, which served as a reference and were adapted and modified to seamlessly integrate within the library.

* Code corrections and enhancements.

-The option to put your own custom html code was added when the connection is accepted and made successfully to obtain the token. If you do not define it by default, the image and text of the following screenshot will remain

example:
s.oauth2.html_code ="<html><head><meta charset=""UTF-8""><title>Successful Connection</title><style>body{text-align:center;background-color:#f0f5f9;color:#333333;font-family:Arial,sans-serif;}h1{color:#007bff;font-size:24px;}img{width:200px;height:200px;}p{margin-bottom:20px;font-size:16px;}</style></head><body><h1>Successful Connection</h1><img src=""https://images.prismic.io/wdr-test-icti/3f9387b0e53fb535624c3a559964e5f3871345ac_connect-logo3x.png?auto=compress,format"" alt=""Logo""><p>You can now return to your application.</p></body></html>"

2023-06-01_090900.png


"For those who have already purchased the previous version, please log in to your user panel on our store using your credentials. Under the 'Recent Orders' section, locate 'Gsheets' or 'Gsheetsplus' and you will find the option to download the latest version again. We hope you enjoy this improved release!"
 
Last edited:

fernando1987

Active Member
Licensed User
Take advantage and get a 30% discount on any product throughout this month with the code STORECODE30
 

fernando1987

Active Member
Licensed User
Version 6.0 now available!


Changes

GSheet​

-Code corrections and enhancements.

-GetSheetDataByColumnValue(sheet_name As String, column_name As String, search_value As String):
Retrieves rows based on a search value.


Example:
Sub Button1_Click
   
    s.GetSheetDataByColumnValue("Example","Name","Javier")
   

End Sub

Private Sub s_DataByColumnValue_success(x As List, success As Boolean)
    If success = True Then
        Log(x)
        For Each col As Map In x
            Log(col.Get("Name"))
            Log(col.Get("Age"))
        Next
    Else

    End If
End Sub

GSheet plus​

-Code corrections and enhancements.

-GetSheetDataByColumnValue(sheet_name As String, column_name As String, search_value As String):
Retrieves rows based on a search value.


Example:
Sub Button1_Click
   
    s.GetSheetDataByColumnValue("Example","Name","Javier")
   

End Sub

Private Sub s_DataByColumnValue_success(x As List, success As Boolean)
    If success = True Then
        Log(x)
        For Each col As Map In x
            Log(col.Get("Name"))
            Log(col.Get("Age"))
        Next
    Else

    End If
End Sub

-GetColumnSum(sheet_name As String, column_name As String):
Calculates the values sum of a specific column.

example:
Sub Button1_Click
   
    s.GetColumnSum("Example","Name")
   

End Sub

Private Sub s_GetColumnSum_success(sum As Double, success As Boolean)
    If success = True Then
        Log(sum)
    Else

    End If
End Sub

-GetSheetColumnCount(sheet_name As String, column_name As String):
Counts the number of non-empty cells in a specific column.

Example:
Sub Button1_Click
   
    s.GetSheetColumnCount("Example","Name")
   

End Sub

Private Sub sColumnCount_success(count As Double, success As Boolean)
    If success = True Then
        Log(count)
    Else

    End If
End Sub

-GetValueCountInColumn(sheet_name As String, column_name As String, value1 As String):
Counts the number of non-empty cells in a specific column based on a search value.

Example:
Sub Button1_Click
   
    s.GetValueCountInColumn("Example","Name","Javier")
   

End Sub

Private Sub s_ValueCountInColumn_success(count As Double, success As Boolean)
    If success = True Then
        Log(count)
    Else

    End If
End Sub

-GetSheetDataByColumnRange(sheet_name As String, column_name As String, operator As String, value As Double):
Retrieves rows from a specific column based on certain numerical conditions (e.g., greater than, less than).

Example:
Sub Button1_Click
   
    s.GetSheetDataByColumnRange("Example","Age",">=",39)
   

End Sub

Private Sub s_DataByColumnRange_success(x As List, success As Boolean)
    If success = True Then
        For Each col As Map In x
            Log(col.Get("Name"))
            Log(col.Get("Age"))
        Next
    Else

    End If
End Sub


"For those who have already purchased the previous version, please log in to your user panel on our store using your credentials. Under the 'Recent Orders' section, locate 'Gsheets' or 'Gsheetsplus' and you will find the option to download the latest version again. We hope you enjoy this improved release!"
 

fernando1987

Active Member
Licensed User
Version 6.1 now available!


Changes


-Code corrections and enhancements.

"For those who have already purchased the previous version, please log in to your user panel on our store using your credentials. Under the 'Recent Orders' section, locate 'Gsheets' or 'Gsheetsplus' and you will find the option to download the latest version again. We hope you enjoy this improved release!"
 

fernando1987

Active Member
Licensed User
Version 7 now available! 🚀

Changes:


  • Code corrections and enhancements. 🛠️
  • Added extraction of the row number each time a query is made. This data, called "id_row", is included in all queries and can be useful for deleting or updating that row. 📊
"For those who have already purchased the previous version, please log in to your user panel on our store using your credentials. Under the 'Recent Orders' section, locate 'Gsheets' or 'Gsheetsplus' and you will find the option to download the latest version again. We hope you enjoy this improved release!" 🎉

Example:
Sub Process_Globals
    Private fx As JFX
    Private MainForm As Form
    Private xui As XUI
    Private Button1 As B4XView
    Dim s As Gsheetsplus
End Sub

Sub AppStart (Form1 As Form, Args() As String)
    MainForm = Form1
    MainForm.RootPane.LoadLayout("Layout1")
    MainForm.Show
    s.Initialize(Me,"googlesheets","xxxxxxx","xxxxxxxx.apps.googleusercontent.com")
    s.SpreadsheetsId = "1FHx-a_a4afTQ3L5hXJIOttCVaJNngO3113E-qqFOjbk"
End Sub

Sub Button1_Click
    s.GetSheetDataByColumnRange("Example","Age","<",26)

End Sub


Private Sub googlesheets_DataByColumnRange_success(x As List, success As Boolean)
    If success = True Then
        For Each col As Map In x
            Log(col.Get("Name"))
            Log(col.Get("Age"))
            Log(col.Get("id_row"))
        Next
    Else

    End If
End Sub
 
Top