Android Question Problem with updating a cell in google sheet

[Abbas818]

Hi
I am trying to communicate with Google sheet. Using the code below I can read data from Cell B3 of sheet One. This code works fine.



Dim j As HttpJob
Dim StrGS As String
j.Initialize("", Me)

j.Download("https://sheets.googleapis.com/v4/sp...3?key=AIzaSyCWv_klAv053_UDSW5SCqh2a7FPamSKRAc")

Wait For (j) JobDone(j As HttpJob)
If j.Success Then
StrGS=j.GetString
LblFromGS.Text=StrGS
End If
j.Release

I tried a lot to update the value of a cell in google sheet but not successful yet. I tested all examples in B4A forum but nothing worked for me. Can everybode help me. I just want a simple code to update the value of one cell for example B3.

 

[DonManfred]

check this library (not free):

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...

www.b4x.com

 

[Abbas818]

check this library (not free):

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...

www.b4x.com

Hi, thanks
It is not possible for me to buy it.
Is there another way?

 

[DonManfred]

Is there another way?

i don´t know another library.

 

[emexes]

It is not possible for me to buy it.
Is there another way?

Does the GSheets library solve your problem?

The library's author Fernando writes "One-time payment, no strings attached! "

Other than the $7, is there anything else stopping you from using it?

 

[DonManfred]

Other than the $7, is there anything else stopping you from using it?

He answered

It is not possible for me to buy it.

to my answer telling about this library from Fernando.

 

[emexes]

He answered to my answer telling about this library from Fernando.

Understood: I'm just trying to distil what the true problem is that makes it not possible for him to buy it, in case there is an easy solution to that secondary issue.

That library looks pretty good, btw, so thanks for the prompt pointing it out.

 

[Abbas818]

Understood: I'm just trying to distil what the true problem is that makes it not possible for him to buy it, in case there is an easy solution to that secondary issue.

That library looks pretty good, btw, so thanks for the prompt pointing it out.

I can not pay by PayPal in my country. Only by Tether I can.

 

[Abbas818]

Does the GSheets library solve your problem?

The library's author Fernando writes "One-time payment, no strings attached! "

Other than the $7, is there anything else stopping you from using it?

Yes, this library can do what i want

 

[emexes]

Yes, this library can do what i want

Wheels are in motion and we are on the home straight. Check your B4X conversations. Check this post.

 

[personalapps]

Hi, thanks
It is not possible for me to buy it.
Is there another way?

You can use Google App Script (GAS) and publish the sheet as web app so you can access it by okhttp easily by WEB APP URL. Here is the GAS if you want:

function doGet(e) {
  const sheet = SpreadsheetApp.openById('google sheet ID').getActiveSheet();
  const action = e.parameter.action;

  if (action === 'read') {
    const data = sheet.getDataRange().getValues();
    return ContentService.createTextOutput(JSON.stringify(data)).setMimeType(ContentService.MimeType.JSON);
  }

  if (action === 'create') {
    const values = JSON.parse(e.parameter.values);
    sheet.appendRow(values);
    return ContentService.createTextOutput('Row added successfully');
  }

  if (action === 'update') {
    const row = parseInt(e.parameter.row, 10);
    const values = JSON.parse(e.parameter.values);
    for (let i = 0; i < values.length; i++) {
      sheet.getRange(row, i + 1).setValue(values[i]);
    }
    return ContentService.createTextOutput('Row updated successfully');
  }

  if (action === 'delete') {
    const row = parseInt(e.parameter.row, 10);
    sheet.deleteRow(row);
    return ContentService.createTextOutput('Row deleted successfully');
  }

  return ContentService.createTextOutput('Invalid action');
}

 

[Abbas818]

You can use Google App Script (GAS) and publish the sheet as web app so you can access it by okhttp easily by WEB APP URL. Here is the GAS if you want:

function doGet(e) {
  const sheet = SpreadsheetApp.openById('google sheet ID').getActiveSheet();
  const action = e.parameter.action;

  if (action === 'read') {
    const data = sheet.getDataRange().getValues();
    return ContentService.createTextOutput(JSON.stringify(data)).setMimeType(ContentService.MimeType.JSON);
  }

  if (action === 'create') {
    const values = JSON.parse(e.parameter.values);
    sheet.appendRow(values);
    return ContentService.createTextOutput('Row added successfully');
  }

  if (action === 'update') {
    const row = parseInt(e.parameter.row, 10);
    const values = JSON.parse(e.parameter.values);
    for (let i = 0; i < values.length; i++) {
      sheet.getRange(row, i + 1).setValue(values[i]);
    }
    return ContentService.createTextOutput('Row updated successfully');
  }

  if (action === 'delete') {
    const row = parseInt(e.parameter.row, 10);
    sheet.deleteRow(row);
    return ContentService.createTextOutput('Row deleted successfully');
  }

  return ContentService.createTextOutput('Invalid action');
}

Hi
Thanks for your response.
I create a new Spreadsheet and add your code to "Apps script" section in "Extensions" menu of the spereadsheet. I run it and allowed the required access to my google account.
This method is correct?
If yes, can you please show me how to update a cell in the spreadsheet using Okhttp?

 

[emexes]

If yes, can you please show me how to update a cell in the spreadsheet using Okhttp?

I am curious about this too. It sounds like it could be useful as a poor man's cloud database.

 

[personalapps]

Hi
Thanks for your response.
I create a new Spreadsheet and add your code to "Apps script" section in "Extensions" menu of the spereadsheet. I run it and allowed the required access to my google account.
This method is correct?
If yes, can you please show me how to update a cell in the spreadsheet using Okhttp?

Click Deploy > New Deployment.
Select Web app.
Under "Who has access," select Anyone
Copy the Web App URL (it will look like this: https://script.google.com/macros/s/.../exec).
Please find attached b4j example for upating row 2. I have given all CRUD operations functions too for you to check.

 

[personalapps]

I am curious about this too. It sounds like it could be useful as a poor man's cloud database.

yes it is very useful ! I have attached example b4j app

 

[personalapps]

If any one wants, just attach the data to this forum . I will deploy in my Google sheet and give the web app url which you can use. Since the whole thing is free, I have no problem.

 

[Abbas818]

Thanks a lot
Your attached example works fine. Using your B4J program I wrote a program in B4a. It works fine too.
I have a question, is it possible to update and read a specific cell for example the cell in Row i and Column j?

 

[Abbas818]

I could update a specific cell by changing your functions. The problem is to read a specific cell.
To read a cell in location (row, col) I changed the function in "B4A" and "App Script" as follows, but it does not work.

In B4A:
Sub ReadData(row As Int, col As Int)
Dim url As String = $"${WEB_APP_URL}?action=read&row="$ & row & "&col=" & col
Dim job As HttpJob
job.Initialize("read", Me)
job.Download(url)
End Sub

In Apps script:
if (action === 'read') {
const row = parseInt(e.parameter.row);
const col = parseInt(e.parameter.col);
const value = sheet.getRange(row, col).getValue();
return ContentService.createTextOutput(JSON.stringify({ value: value })).setMimeType(ContentService.MimeType.JSON);
}

 

[aaronk]

Try something like the below. I haven't tried it for a little while but it used to work when I was playing around with Google Sheets earlier this year.

It will get the values in that range. You should be able to select just one cell if required by setting RangeSelection to the single cell.

Dim job As HttpJob
    job.Initialize("SendAssets", Me)

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

    Dim SheetName As String = "Sheet1"
    Dim RangeSelection As String = "A1:B5"
    
    Dim SheetID As String = $"1veu.........uoY64raOEQ"$ ' Change SheetID to the spreadsheet you are using.
    Dim Range As String = $"${SheetName}!${RangeSelection}"$
    
    job.Download($"https://sheets.googleapis.com/v4/spreadsheets/${SheetID}/values/${Range}?majorDimension=ROWS"$)
    job.GetRequest.SetHeader("Authorization","Bearer " & Token)

    Wait For (job) JobDone(job As HttpJob)
    If job.Success = True Then
        Log(job.GetString)
    End If

 

[personalapps]

I could update a specific cell by changing your functions. The problem is to read a specific cell.
To read a cell in location (row, col) I changed the function in "B4A" and "App Script" as follows, but it does not work.

In B4A:
Sub ReadData(row As Int, col As Int)
Dim url As String = $"${WEB_APP_URL}?action=read&row="$ & row & "&col=" & col
Dim job As HttpJob
job.Initialize("read", Me)
job.Download(url)
End Sub

In Apps script:
if (action === 'read') {
const row = parseInt(e.parameter.row);
const col = parseInt(e.parameter.col);
const value = sheet.getRange(row, col).getValue();
return ContentService.createTextOutput(JSON.stringify({ value: value })).setMimeType(ContentService.MimeType.JSON);
}

You probably haven't redeployed after saving the script.
You have to save the Google App Script and redeploy it:
Open your Google Apps Script project.
Go to Deploy > Manage Deployments.
Find your current deployment and click the pencil icon to edit it.
Click 'Deploy' again to redeploy the app.
Copy the Web App URL (if it changes) and use it in your B4X app.