Android Question Google Sheets Append Data errors

IndieDev

Active Member
Licensed User
Hi,

Been trying to append data to end of Google Sheets.
This is my Update function.
B4X:
Private Sub UpdateSheetData(sValueRange As String, sValues 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 sSTR As String = "https://sheets.googleapis.com/v4/spreadsheets/" & sheetID & _
                                "/values/" & sValueRange & _
                                ":append?valueInputOption=RAW"
        Log("sSTR: " & sSTR & CRLF & " sValues: " & "{""values"":[[" & sValues & "]]}")
 
        Dim j As HttpJob
        j.Initialize("", Me)
        j.PutString(sSTR, "{""values"":[[" & sValues & "]]}")

        j.GetRequest.SetHeader("Authorization", "Bearer " & Token)
        j.GetRequest.SetHeader("Accept", "application/json")
        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
"STOPPAGES" is the worksheet.

Keep getting error....
B4X:
404. That’s an error.

The requested URL /v4/spreadsheets/***************************/values/STOPPAGES:append?valueInputOption=RAW was not found on this server. That’s all we know.

The logs display the correct strings...
B4X:
sSTR: https://sheets.googleapis.com/v4/spreadsheets/***************************/values/STOPPAGES:append?valueInputOption=RAW

sValues: {"values":[["Feb-21","11-2-21"]]}

Where am I going wrong?

Regards.

NOTE: I've "starred" the SheetID.
 
Last edited:

IndieDev

Active Member
Licensed User
This the code displayed when I try on Google Sheets API page here (https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append)

B4X:
curl --request POST \
  'https://sheets.googleapis.com/v4/spreadsheets/***************************/values/STOPPAGES:append?valueInputOption=RAW' \
  --header 'Authorization: Bearer [YOUR_ACCESS_TOKEN]' \
  --header 'Accept: application/json' \
  --header 'Content-Type: application/json' \
  --data '{"values":[["MAY-21","11-05-2021"]]}' \
  --compressed
 
Upvote 0

IndieDev

Active Member
Licensed User
Found the error from my side. (PEBCAK :) )

Posting this here, in case someone else also faces this same issue...

Changed the following code
B4X:
        j.PutString(sSTR, "{""values"":[[" & sValues & "]]}")

To
B4X:
        j.PostString(sSTR, "{""values"":[[" & sValues & "]]}")

Now, I'm able to append to end of the Google Sheet!!!
 
Upvote 0

IndieDev

Active Member
Licensed User
Indie, are u willing to share ur project? I'm new to B4A and am interested in accessing google sheets. This would be a great start. TIA.

Hi Tomm,

Can't upload the whole project, but will insert some code snippets below.

Libraries used:
  • Core
  • JSON
  • OkHttpUtils2
  • Phone
  • RandomAccessFile
  • Reflection
  • XUI
  • XUI Views
Additionally, you will require "GoogleOAuth2.bas" by Erel (Link)

You will have to set your own values for the following, from Google:
ClientID, API_KEY, sheetID

This is not very good code, I'm afraid, but, it should help get you started.
Won't be able to help you beyond this.

Start coding, hunt around in the B4X forums, you'll get there. ;)

B4X:
Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.
    Private xui As XUI
    Public ClientID As String = "27XXXXXXX-XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX.apps.googleusercontent.com"
    Public oauth2 As GoogleOAuth2
    Public API_KEY As String = "AzzzzzzzzzZZZZZZzzzzzZZZZZZZZQ"
    Public sheetID As String = "1XXXxxxXXXXx-exxXXXxx00XXxx_xXXxxxX"
    Public PWS As PhoneWakeState
End Sub


Sub Activity_Create(FirstTime As Boolean)
    Activity.LoadLayout("Layout")

    If FirstTime Then
        oauth2.Initialize(Me, "oauth2", ClientID, "https://www.googleapis.com/auth/spreadsheets profile")
        If Not (oauth2.TokenValid) Then
            ToastMessageShow("Loading Google Authentication dialog." & CRLF & CRLF & "Please sign in with your Google account.", True)
        End If
        '### uncomment line below if you changed the scope or want to reset the token
'        oauth2.ResetToken

        DateTime.DateFormat="dd-MM-yyyy"
        Dim sDay As String = DateTime.GetDayOfMonth(SDCalendarDialog1.DatePicker)
        Dim sMonth As String = DateTime.GetMonth(SDCalendarDialog1.DatePicker)
        Dim sYear As String = DateTime.Getyear(SDCalendarDialog1.DatePicker)
        sYear = sYear.SubString(sYear.Length - 2)

        mGlobal.sVersion = mGlobal.getVersion
        sDate = sDay & "-" & sMonth & "-" & sYear
        DateTime.DateFormat = "MMM-yy"
        sMonthYear = DateTime.Date(SDCalendarDialog1.DatePicker)
        DateTime.DateFormat="dd-MM-yyyy"
    End If
  
    LoadSpinners

End Sub

Sub Activity_Resume
    oauth2.CallFromResume(Activity.GetStartingIntent)
    PWS.KeepAlive(True)
End Sub

Sub Activity_Pause (UserClosed As Boolean)
    PWS.ReleaseKeepAlive
End Sub

Private Sub LoadSpinners

    ProgressDialogShow2("Downloading data...", False)
  
    oauth2.GetAccessToken
    Wait For OAuth2_AccessTokenAvailable (Success As Boolean, Token As String)
    If Success = False Then
        ToastMessageShow("Error accessing account.", True)
        ProgressDialogHide
        Return
    End If
      
    '####### PEOPLE API #######
    Dim j As HttpJob
    j.Initialize("", Me)
  
    'full list of features available: person.addresses,person.age_ranges,person.biographies,person.birthdays,person.bragging_rights,person.cover_photos,person.email_addresses,person.events,person.genders,person.im_clients,person.interests,person.locales,person.memberships,person.metadata,person.names,person.nicknames,person.occupations,person.organizations,person.phone_numbers,person.photos,person.relations,person.relationship_interests,person.relationship_statuses,person.residences,person.skills,person.taglines,person.urls
    j.Download2("https://people.googleapis.com/v1/people/me", Array As String("access_token", Token, "requestMask.includeField", "person.names"))
  
    Wait For (j) JobDone(j As HttpJob)
    If j.Success Then
        ParsePersonData(j.GetString)
    Else
        oauth2.ResetToken
        ToastMessageShow("Online data not available.", True)
    End If
    j.Release
    '###########################

    '####### Get range of values for Spinner #######
    Dim range As String = "LIST!I2:I"
    Dim sSTR As String = "https://sheets.googleapis.com/v4/spreadsheets/" & sheetID & _
                        "?includeGridData=true&ranges=" & range

    Dim j As HttpJob
    j.Initialize("", Me)
    j.Download(sSTR)
    j.GetRequest.SetHeader("Authorization", "Bearer " & Token)

    Wait For (j) JobDone(j As HttpJob)
  
    If j.Success Then
'        Log(j.GetString)
        Spn_01.clear
        Spn_01.Add("Select")
      
        Dim parser As JSONParser
        parser.Initialize(j.GetString)
        Dim root As Map = parser.NextObject
        Dim sheets As List = root.Get("sheets")
      
        For Each colsheets As Map In sheets
            Dim data As List = colsheets.Get("data")
          
            For Each coldata As Map In data
                Dim rowData As List = coldata.Get("rowData")
              
                For Each colrowData As Map In rowData
                    Dim values As List = colrowData.Get("values")
                  
                    For Each colvalues As Map In values
                        Dim effectiveValue As Map = colvalues.Get("effectiveValue")
                      
                        If effectiveValue.IsInitialized Then
                            Dim stringValue As String = effectiveValue.Get("stringValue")
                            Spn_01.Add(stringValue)
                        End If
                    Next
                Next
            Next
        Next
    End If
    j.Release
    '###########################
  
    ProgressDialogHide

End Sub

private Sub Btn_Update_Click
        UpdateSheetData("GsheetName", _
                            """" & sMonthYear & """,""" & _
                            Txt_Qty.Text & """")
End Sub

Private Sub UpdateSheetData(sValueRange As String, sValues As String)
    Try
        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 sSTR As String = "https://sheets.googleapis.com/v4/spreadsheets/" & sheetID & _
                                "/values/" & sValueRange & _
                                ":append?valueInputOption=RAW"
  
        Dim j As HttpJob
        j.Initialize("", Me)
        j.PostString(sSTR, "{""values"":[[" & sValues & "]]}")

        j.GetRequest.SetHeader("Authorization", "Bearer " & Token)
        j.GetRequest.SetHeader("Accept", "application/json")
        j.GetRequest.SetContentType("application/json")
  
        Wait For (j) JobDone(j As HttpJob)
        If j.Success Then
            Log("Response Length:" & j.GetString.Length & " bytes")
            Log(j.GetString)
            sJSON = j.GetString
            GetCurrentRow
            UpdateIndVals(Token)
            ToastMessageShow("Data updated on server.", False)
            ClearFields
        Else
            Log(j.ErrorMessage)
            ToastMessageShow("Couldn't update data." & CRLF & "Error:" & j.ErrorMessage, True)
        End If
      
    Catch
        Log(LastException)
        ToastMessageShow(LastException, True)
    End Try

    j.Release
  
End Sub

Sub GetCurrentRow
    Dim parser As JSONParser
    parser.Initialize(sJSON)
    Dim root As Map = parser.NextObject
    Dim updates As Map = root.Get("updates")
    Dim updatedRange As String = updates.Get("updatedRange")
    Dim indx As Int = updatedRange.IndexOf(":")
    sRow = updatedRange.SubString(indx + 2)
    Log("sRow: " & sRow)
End Sub

Sub UpdateIndVals(Token As String)
    Dim sErrCode As String
  
    If Spn_01.SelectedItem <> "Select" Then
        sErrCode = Spn_01.SelectedItem.CharAt(0)
        PutVals(Token, mGlobal.returnColName(sErrCode) & sRow, Txt_Minutes01.Text)
    End If
End Sub

Sub PutVals(Token As String, RowCol As String, sValues As String)
    Dim sSTR As String = "https://sheets.googleapis.com/v4/spreadsheets/" & sheetID & _
                                "/values/STOPPAGES!" & RowCol & _
                                "?valueInputOption=RAW"
    Log("sSTR: " & sSTR)
  
    Dim j As HttpJob
    j.Initialize("", Me)
    j.PutString(sSTR, "{""values"":[[" & sValues & "]]}")

    j.GetRequest.SetHeader("Authorization", "Bearer " & Token)
    j.GetRequest.SetHeader("Accept", "application/json")
    j.GetRequest.SetContentType("application/json")
  
    Wait For (j) JobDone(j As HttpJob)
    If j.Success Then
        Log("Response Length:" & j.GetString.Length & " bytes")
        Log(j.GetString)
    Else
        Log(j.ErrorMessage)
        ToastMessageShow("Couldn't update data." & CRLF & "Error:" & j.ErrorMessage, True)
    End If
  
    j.Release

End Sub

Sub ParsePersonData (data As String)
    Dim jp As JSONParser
    jp.Initialize(data)
  
    Dim map As Map = jp.NextObject
    Dim names As List = map.Get("names")
  
    If names.Size > 0 Then
        Dim name As Map = names.Get(0)
        sUsername = name.Get("displayName")
        Lbl_Username.Text = sUsername
    End If
End Sub
 
Last edited:
Upvote 0
Top