Android Question Accessing data from Google Sheets

trueboss323

Active Member
Licensed User
Longtime User
Is there a simple way to read and write a spreadsheet from online Google Sheets? I made it public so it should be accessible with a shared link.
 

trueboss323

Active Member
Licensed User
Longtime User
So something like this then?

B4X:
Sub DownloadFile()
    'download csv file
    Dim j As HttpJob
    j.Initialize("", Me)
    j.Download2($"https://www.googleapis.com/drive/v3/files/1mXHtpYyajqgrX-BKEeoX9lFdtBuq8DSt-P3G40pC8IM/export"$, _
     Array As String("mimeType", "text/csv"))
    Wait For (j) JobDone(j As HttpJob)
    If j.Success Then
        Log(j.GetString)
    End If
    j.Release
End Sub

But I'm not sure how I get the the right File ID.
 
Upvote 0

trueboss323

Active Member
Licensed User
Longtime User
Thanks Don and Erel. I figured how to get values from a Spreadsheets documents:
B4X:
Sub GetSheet(range As String)
    'download csv file
    Dim apikey As String = "..."
    Dim j As HttpJob
    j.Initialize("", Me)
    j.Download2($"https://sheets.googleapis.com/v4/spreadsheets/<SheetId>/values/"$&range, _
     Array As String("key", apikey))
    Wait For (j) JobDone(j As HttpJob)
    If j.Success Then
        'Log(j.GetString)
        Dim parser As JSONParser
        parser.Initialize(j.GetString)
        Dim root As Map = parser.NextObject
        Dim values As List = root.Get("values")
        Dim cindex, rindex As Int
        For Each rowvalues As List In values    'get row
            rindex=rindex+1
            'Log("row: " & rowvalues)

            For Each colvalues As String In rowvalues    'get column
                cindex=cindex+1
                Log(colvalues)
                Log("rindex: " & rindex & "cindex: "&cindex)
            Next
            cindex = 0
        Next

     
    End If
    j.Release
End Sub
With updating values I still need help on.
 
Last edited:
Upvote 0

trueboss323

Active Member
Licensed User
Longtime User
Ok, I finally figured it out, it was a lot of work. It works, but I'm not sure if it's the ideal solution. Can anyone check my code?
B4X:
Sub UpdateSheet(range As String, value As String)

    oauth2.GetAccessToken
    Wait For OAuth2_AccessTokenAvailable (Success As Boolean, Token As String)
    If Success = False Then
        ToastMessageShow("Error accessing account.", True)
        Return
    End If

    Dim j As HttpJob
    j.Initialize("", Me)
    j.PutString("https://sheets.googleapis.com/v4/spreadsheets/<SheetID>/values/"&range&"?valueInputOption=RAW&access_token=" & Token, _
    "{""values"":[["""&value&"""]]}")

    j.GetRequest.SetContentType("application/json")
    Wait For (j) JobDone(j As HttpJob)
    If j.Success = True  Then
        Log("Response Length:" & j.GetString.Length & " bytes")
        Log(j.GetString)
    End If
    j.Release
End Sub
 
Upvote 0

DonManfred

Expert
Licensed User
Longtime User
Upvote 0

trueboss323

Active Member
Licensed User
Longtime User
What is the example call of this call?

B4X:
Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.
    Dim ClientId As String = "..."
    Private oauth2 As GoogleOAuth2

End Sub
B4X:
Sub Activity_Create(FirstTime As Boolean)
   'Do not forget to load the layout file created with the visual designer. For example:
   If FirstTime Then   
       oauth2.Initialize(Me, "oauth2", ClientId, "https://www.googleapis.com/auth/spreadsheets")
   End If

   Activity.LoadLayout("1")

   UpdateSheet("A3", "Updated with b4a")

End Sub
 
Upvote 0

trueboss323

Active Member
Licensed User
Longtime User
Ideally I would like to try and remove the OAuth2 library . Since the sheet is public , no sign in is required and anyone can edit it.
 
Upvote 0
Cookies are required to use this site. You must accept them to continue using the site. Learn more…