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:

[class][B4X] Google OAuth2

Edit: The Android implementation will not work with Google services due to new restrictions. Use GoogleAuthorization instead with B4A: https://www.b4x.com/android/forum/threads/google-authorization-with-authorizationclient.169164/ It is no longer possible to use WebView to implement Google's...

www.b4x.com

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:

CreateResourceFromFile(Macro, FirebaseAnalytics.GooglePlayBase)

Read example for v4 api:

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

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:

#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(Me, "s")'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:

https://b4xapp.com/en/producto/390

Download Gsheets:

https://b4xapp.com/item/gsheets-

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

B4J Example google sheet:
Sheet for example


B4A Example google sheet:
Sheet for example

 

[fernando1987]

Here are the examples of the stacked apps so you can perform live tests:

And this is the sheet where you will see the changes:
Google sheets api example

 

[fernando1987]

Google Sheet to db

Sub GetSheet_to_db
 
    Dim j As HttpJob
    j.Initialize("", Me)
    j.Download("https://gsheets2json.xyz/api?api_key=XXXXXXXXXXXXXXXXXXX&id=1FHx-a_a4afTQ3L5hXJIOttCVaJNngO3113E-qqFOjbk&columns=false&sheet=Example")
    Wait For (j) JobDone(j As HttpJob)
    If j.Success = True Then
        Dim s1 As String = j.GetString.Trim
        Dim m As Map = s1.As(JSON).ToMap
        Dim rows As List = m.Get("rows")
        For Each colrows As Map In rows
            Dim id1 As Int = colrows.Get("1")
            Dim create As String = colrows.Get("created")
            Dim Telephone1 As Int = colrows.Get("Téléphone")
            Dim Age1 As Int = colrows.Get("Age")
            Dim Name1 As String = colrows.Get("Name")
            If colrows.Size > 0 Then
            Private maps As List
            Private mp As Map
            maps.Initialize
            mp.Initialize
            mp.put("id",id1)
            mp.put("telephone",telephone1)
            mp.put("name",Name1)
            mp.put("age",Age1)
            mp.put("create",create)         
              maps.Add(mp)
              DBUtils.InsertMaps(Main.SQL,"dbname",maps)
          
            End If
        Next
    Else
  
    End If

End Sub

 

[fernando1987]

Implementation example

 

[fernando1987]

New feature added in GSheetsplus.

GSheetsplus
Version: 1.10

Functions:

• DBtoSpreadsheet(DBname As String, Tablename As String, titlesheet As List, columnsdb As List)
  Convert a sqlitle base into a google sheet

Example:

Private Sub Button2_Click

 Dim l2 As List
    l2.Initialize
    l2.Clear
    l2.Add(Array As String("telephone","name","Age","Create","=ARRAYFORMULA(SI(A1:A<>"""";FILA(A1:A);""""))")) 'Place this Formula at the end so that you can retrieve the row number
 
    Dim c As Cursor
    c = Starter.SQL1.ExecQuery("SELECT* FROM sheets")
   
   
    Dim l1 As List
    l1.Initialize
    l1.Clear
    If c.RowCount>0 Then
       
        For i=0 To c.RowCount-1
            c.Position=i
       
l1.Add(Array As String(c.GetString("telephone"), _
c.GetString("name"), _
c.GetString("age"), _
c.GetString("date")))  
   
 
        Next

    end  If
    c.Close
    Try
       
s.DBtoSpreadsheet("My DB","Users",l2,l1)

    Catch
   
        End Try
        Sleep(5000)
        dim datatableid as string
    datatableid = s.newspreadsheetsid 'retrieves the id of the newly created spreadsheet so that you can store it a sleep(5000) is recommended and then retrieves it to store it and be able to work on the newly created spreadsheet
ToastMessageShow(datatableid,True)  
    loadtable
End Sub

 

[fernando1987]

New feature added

GSheetsplus
Version: 1.2
GSheets
Version: 1.2

Changes:

• Now you can work with formulas sent from the application to the google sheet.
• Error dialog boxes were implemented that will see if a successful connection was not made or the data was not sent. (Screenshots are attached)
• The Google OAuth2 class was added to the library and you no longer need to add it to your projects

B4A, B4I

• Initialize (Activity,ClientId As String)

b4xpages (B4A, B4I)

• Initialize (Root,ClientId As String)

Additional libraries (B4J):
CSSUtils
JavaObject

Download Gsheetsplus:

https://b4xstore.xyz/item/gsheetplus-

Download Gsheets:

Gsheets

b4xstore.xyz

 

[fernando1987]

New feature added in GSheetsplus and GSheets library.

GSheetsplus
GSheets
Version: 2.00

Functions:

• delete_range(start_id As Int, end_id As Int)

remove a range of rows from the table

• UpdateSheet_multi_range(ranges As List, values As List)

update more than one range in google sheets
EXAMPLE:

Private Sub Button2_Click

Try


        Dim l As List
        l.Initialize
        Dim l2 As List
        l2.Initialize
        l.AddAll(Array As String("Hello","Example3","Example2","Example1"))
        l2.AddAll(Array As String("Example!B5","Example!A8","Example!A9","Example!A3"))

s.UpdateSheet_multi_range(l2,l)

    Catch
        Log("error")
        End Try
       
End Sub

• UpdateSheet_cell(cell As String, value As String)

update a specific cell

• insert_cell(cell As String, value As String)

add a data in a specific cell

• sheets_to_cvs(name As String)

Download the google spreadsheet in csv format (tested in B4J)

 

[le_toubib]

Examples:
B4A:

hi fernando
i got this error when i just downloaded this example :
GoogleOAuth2 is declared twice. You should either remove the library reference or the code module.
any help ??
Edit : i just removed googleauth2 module and added libraries (cssutils, and javaobject), now ok .. thanks

 

[le_toubib]

worked like charm , great work ?

 

[DonManfred]

Paid pro version Transaktionscode 4W5738108H5386203 (msy1912@****.***). Already found downloadlink.
Thank you for writing the Library ?

 

[fernando1987]

Paid pro version Transaktionscode 4W5738108H5386203 (msy1912@****.***). Already found downloadlink.
Thank you for writing the Library ?

Thank you for purchasing the library, every time I update any of my libraries or codes, it will be enough for you to download it from the link in your user profile and you will have the new version ready.

 

[DonManfred]

Thank you for purchasing the library, every time I update any of my libraries or codes, it will be enough for you to download it from the link in your user profile and you will have the new version ready.

I can not see any Example-Code in the Download (it´s only a B4XLib-file). Are there some examples available?

 

[fernando1987]

I can not see any Example-Code in the Download (it´s only a B4XLib-file). Are there some examples available?

Example B4A:

Example google sheet:
Example api

 

[fernando1987]

Thanks to all the people who have purchased my bookstores, I have received a lot of support from this community. I decided to create a discount coupon valid for one week with a 20% discount on any item in the store.
Coupon: B4XSTORE20

 

[angel_]

When I try an example I get this warning:

 

[fernando1987]

When I try an example I get this warning:

View attachment 134810

View attachment 134811

Please review post #8 of this publication and download the examples from post #1, these examples do not need any modification, do not forget to create your passwords in google console... if you have any other questions, let me know

 

[angel_]

In example B4A this warning appears:

dialog1 is declared twice. You should either remove the library reference or the code module.

 

[fernando1987]

dear, please re-download the example from the first post, if you have any other problem please let me know

 

[angel_]

dear, please re-download the example from the first post, if you have any other problem please let me know

It works very well, thank you

 

[fernando1987]

All the source codes and libraries of the store until the last day of the month of November will be at a 50% discount with the following coupon:

B4XSTORE50​