Android Tutorial CREATE, UPDATE, DELETE AND CONSULT REGISTERS ON GOOGLE SHEET FROM YOUR APPLICATIONS

Searching the internet I found a service that allows you to convert any google sheet into a json api, it has a free version that allows you to consult and add data, connecting to the api is quite simple, as some of us have thought of using google sheet integrated to our developments by I want to show how this tool works.

1. Create a new google spreadsheet
2. They share the spreadsheet and if they want to be able to edit, delete and update data, they enable the permissions on the spreadsheet
3. register for free at https://sheet.best Add a new sheet give it a name and copy the link of the shared sheet, and your API is ready to use and test

Se necesitan las librerías JSON y OkhttpUtils2

This is the query code for the api:
sub API
Dim j As HttpJob
    j.Initialize("", Me)
    j.Download("https://sheet.best/api/sheets/cf969697-682a-40e3-bad4-d54803eeeacf") 'change the address for your api'
    Wait For (j) JobDone(j As HttpJob)
    If j.Success Then
        lvgeneral.Clear
        Dim parser As JSONParser
        parser.Initialize(j.GetString.Trim)
        Dim root As List = parser.NextArray
        For Each colroot As Map In root
            Dim Id As String = colroot.Get("Id")
            Dim Age As String = colroot.Get("Age")
            Dim Name As String = colroot.Get("Name")
            'Dim Created_at As String = colroot.Get("Created at")
            lvgeneral.AddTwoLinesAndBitmap(Id, "Name: " & Name & " Age: " &Age , LoadBitmap(File.DirAssets,"usuario.png"))
            lvgeneral.TwoLinesAndBitmap.SecondLabel.TextColor = 0xFF000000
        Next
    End If

This is the Update code for the api:
sub Update
Dim mylist As List
    mylist.Initialize
    Dim m As Map


    m.Initialize
    m.Put("Id", lb_id.text)
    m.Put("Age", lb_age.text)
    m.put("Name", lb_name.Text)
    m.put("Created at", "Fernando")
    mylist.Add(m)
    'Next
'    Cursor1.Close

    Dim j As JSONGenerator
    j.Initialize2(mylist)
  
    Dim job As HttpJob
    job.Initialize("SendAssets", Me)
    Dim text1 As JSONGenerator
    text1.Initialize(m)
    Dim SS As String
    SS = text1.ToPrettyString(1)
    job.PostString("https://sheet.best/api/sheets/cf969697-682a-40e3-bad4-d54803eeeacf", SS)

    job.GetRequest.SetContentType("application/json")


    Wait For (job) JobDone(job As HttpJob)
    If job.Success = True  Then
        Log("Response Length:" & job.GetString.Length & " bytes")
        Log(job.GetString)
    End If
End Sub
 

Erel

B4X founder
Staff member
Licensed User
Longtime User
I have a console B4J program that downloads the IDE translation table every 10 minutes and processes it.

The relevant code is:
B4X:
    oauth2.Initialize(Me, "oauth2", ClientId, "https://www.googleapis.com/auth/drive https://www.googleapis.com/auth/drive.file https://www.googleapis.com/auth/drive.readonly", _
        ClientSecret, DataFolder)

...

oauth2.GetAccessToken
    Wait For OAuth2_AccessTokenAvailable (Success As Boolean, Token As String)
    If Success = False Then
        Log("Error accessing account.")
        Return
    End If
    Dim j As HttpJob
    j.Initialize("", Me)
    j.Download2($"https://www.googleapis.com/drive/v3/files/1fAqr-PxxxxxxxxxA/export"$, _
         Array As String("access_token", Token, "mimeType", "text/csv"))
    Wait For (j) JobDone(j As HttpJob)
 

cambopad

Active Member
Licensed User
Longtime User
I have a console B4J program that downloads the IDE translation table every 10 minutes and processes it.

The relevant code is:
B4X:
    oauth2.Initialize(Me, "oauth2", ClientId, "https://www.googleapis.com/auth/drive https://www.googleapis.com/auth/drive.file https://www.googleapis.com/auth/drive.readonly", _
        ClientSecret, DataFolder)

...

oauth2.GetAccessToken
    Wait For OAuth2_AccessTokenAvailable (Success As Boolean, Token As String)
    If Success = False Then
        Log("Error accessing account.")
        Return
    End If
    Dim j As HttpJob
    j.Initialize("", Me)
    j.Download2($"https://www.googleapis.com/drive/v3/files/1fAqr-PxxxxxxxxxA/export"$, _
         Array As String("access_token", Token, "mimeType", "text/csv"))
    Wait For (j) JobDone(j As HttpJob)
Is it possible to insert or update the data in the sheet?
 

fernando1987

Active Member
Licensed User
¿Puedes compartir el ejemplo completo del video? ¡Gracias!

Por supuesto, adjunto el ejemplo con algunas modificaciones porque si lo paso con mi hoja se me deducirán las transacciones de la 2000 de la cuenta gratuita para que cambie a la hoja de muestra ofrecida por sheet.best , la única limitación es que la parte de eliminación no funcionará, no por la programación, sino porque ese ejemplo está configurado para que no se pueda eliminar de la aplicación de la misma manera si añaden su propia api nota de que si van a poder eliminar filas, además de optimizar el uso de solicitudes en las solicitudes de consulta modificarlas para que se realicen utilizando la API gratuita de gsx2json.com para la que es necesario que la hoja se publique en la web además de ser compartida.
aunque la página limita la cuenta gratuita a sólo leer y agregar de b4a si es posible eliminar y actualizar el ejemplo que hice desde una cuenta gratuita.
 

Attachments

  • Example_google_sheet.zip
    182.7 KB · Views: 598
Last edited:

fernando1987

Active Member
Licensed User
To use it in a better way, it is recommended to work in a local database and then synchronize since, no matter how many rows are sent, the data is counted as a single action as detailed in this second video where I work in a local database and I send the content of the database the google sheet which will prevent the 2000 free shares allowed per month from spending so fast.
This is the code of the button to send data to the google sheet:
Sub Send_json
    Dim c As Cursor
    Dim s As SQL
    Dim mylist As List
    mylist.Initialize
    Dim m As Map
    s.Initialize(File.DirRootExternal,"ejemplo.db",False)
    
    c = s.ExecQuery("SELECT* FROM google")
    If c.RowCount>0 Then
        For i=0 To c.RowCount-1
            c.Position=i
    
            m.Initialize
            m.Put("Id", c.GetString("Id"))
            m.Put("Age", c.GetString("Age"))
            m.put("Name", c.GetString("Name"))
            m.put("Created at", "Fernando")
            mylist.Add(m)
            
        Next
        Dim j As JSONGenerator
        j.Initialize2(mylist)
    
    end  If
    
    
    Dim job As HttpJob
    job.Initialize("SendAssets", Me)
    Dim text1 As JSONGenerator
    text1.Initialize(m)
    Dim SS As String
    SS = j.ToPrettyString(1)
    

    Log(SS)
    Dim job As HttpJob
    
    job.Initialize("SendAssets", Me)
    
    job.PostString("https://sheet.best/api/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/tabs/certificateInformation", SS)

    job.GetRequest.SetContentType("application/json")


    Wait For (job) JobDone(job As HttpJob)
    If job.Success = True  Then
        Log("Response Length:" & job.GetString.Length & " bytes")
        Log(job.GetString)
    End If
End Sub
 

fernando1987

Active Member
Licensed User
I apologize to everyone for my writing, but I am from equator and my native language is Spanish not English, I use the translator of google I hope you can understand me and serve you this method if someone knows a better way or reading the ducumentacion of the page knows something that I miss please do not hesitate to publish it thank you!!!
 

AnandGupta

Expert
Licensed User
Longtime User
Do not worry about language. Google has broken all barriers of language different.
Mine is Hindi/English but I reply also in Spanish/Portugese etc., where required.

We understand all languages now and the world is truly one.

Regards,

Anand
 

fernando1987

Active Member
Licensed User
Do not worry about language. Google has broken all barriers of language different.
Mine is Hindi/English but I reply also in Spanish/Portugese etc., where required.

We understand all languages now and the world is truly one.
thanks, this is the example that I use saving the data in a local base and then sending them massively the interesting thing about this is that thousands of records can be sent there are no limitations and it will only count as an action the bad thing that this does not apply to delete or update for which you must define the value although you can update several values with the same search criteria or eliminate stop by not comparing a json file.
 

Attachments

  • Base_de_Datos__a_Json.zip
    97.1 KB · Views: 444
Last edited:

asales

Expert
Licensed User
Longtime User
I apologize to everyone for my writing, but I am from equator and my native language is Spanish not English, I use the translator of google I hope you can understand me and serve you this method if someone knows a better way or reading the ducumentacion of the page knows something that I miss please do not hesitate to publish it thank you!!!
Thanks!
Obrigado!
Gracias!
 

Javier Campo Martinez

Member
Licensed User
Searching the internet I found a service that allows you to convert any google sheet into a json api, it has a free version that allows you to consult and add data, connecting to the api is quite simple, as some of us have thought of using google sheet integrated to our developments by I want to show how this tool works.

1. Create a new google spreadsheet
2. They share the spreadsheet and if they want to be able to edit, delete and update data, they enable the permissions on the spreadsheet
3. register for free at https://sheet.best Add a new sheet give it a name and copy the link of the shared sheet, and your API is ready to use and test

Se necesitan las librerías JSON y OkhttpUtils2

This is the query code for the api:
sub API
Dim j As HttpJob
    j.Initialize("", Me)
    j.Download("https://sheet.best/api/sheets/cf969697-682a-40e3-bad4-d54803eeeacf") 'change the address for your api'
    Wait For (j) JobDone(j As HttpJob)
    If j.Success Then
        lvgeneral.Clear
        Dim parser As JSONParser
        parser.Initialize(j.GetString.Trim)
        Dim root As List = parser.NextArray
        For Each colroot As Map In root
            Dim Id As String = colroot.Get("Id")
            Dim Age As String = colroot.Get("Age")
            Dim Name As String = colroot.Get("Name")
            'Dim Created_at As String = colroot.Get("Created at")
            lvgeneral.AddTwoLinesAndBitmap(Id, "Name: " & Name & " Age: " &Age , LoadBitmap(File.DirAssets,"usuario.png"))
            lvgeneral.TwoLinesAndBitmap.SecondLabel.TextColor = 0xFF000000
        Next
    End If

This is the Update code for the api:
sub Update
Dim mylist As List
    mylist.Initialize
    Dim m As Map


    m.Initialize
    m.Put("Id", lb_id.text)
    m.Put("Age", lb_age.text)
    m.put("Name", lb_name.Text)
    m.put("Created at", "Fernando")
    mylist.Add(m)
    'Next
'    Cursor1.Close

    Dim j As JSONGenerator
    j.Initialize2(mylist)
 
    Dim job As HttpJob
    job.Initialize("SendAssets", Me)
    Dim text1 As JSONGenerator
    text1.Initialize(m)
    Dim SS As String
    SS = text1.ToPrettyString(1)
    job.PostString("https://sheet.best/api/sheets/cf969697-682a-40e3-bad4-d54803eeeacf", SS)

    job.GetRequest.SetContentType("application/json")


    Wait For (job) JobDone(job As HttpJob)
    If job.Success = True  Then
        Log("Response Length:" & job.GetString.Length & " bytes")
        Log(job.GetString)
    End If
End Sub

Hi Fernando !!!

AFAIK, the free API generated by https://sheet.best only receives 200 requests by month in the free plan. If i need more than that, i need to pay or to wait the next month. Am i wrong? By the way, i used it and it works, but I think I will need more than 200 requests a month.
 

fernando1987

Active Member
Licensed User
Hi Fernando !!!

AFAIK, the free API generated by https://sheet.best only receives 200 requests by month in the free plan. If i need more than that, i need to pay or to wait the next month. Am i wrong? By the way, i used it and it works, but I think I will need more than 200 requests a month.
Maybe this is a better solution:
 
Top