Android Question Accessing data from Google Sheets

[trueboss323]

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.

 

[Erel]

You can download it as CSV file with OkHttpUtils2.

Something like:

j.Download2($"https://www.googleapis.com/drive/v3/files/1fAqr-POOiNz_N_GCtBxxxxxxxxxxhOfktKrJG4A/export"$, _
     Array As String("mimeType", "text/csv"))

 

[trueboss323]

So something like this then?

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.

 

[DonManfred]

https://developers.google.com/sheets/api/reference/rest

 

[trueboss323]

Thanks Don and Erel. I figured how to get values from a Spreadsheets documents:

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.

 

[trueboss323]

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?

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

 

[DonManfred]

It works

What is the example call of this call?

Can anyone check my code?

I´ll try it the next days for sure. Never need it though but i´m interested on how it works as i do have a sheet online.

 

[trueboss323]

What is the example call of this call?

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

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

 

[trueboss323]

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.