Android Question Problem with updating a cell in google sheet

Abbas818 · Dec 17, 2024

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 · Dec 17, 2024

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 · Dec 17, 2024

DonManfred said:
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 · Dec 17, 2024

Abbas818 said:
Is there another way?

i don´t know another library.

emexes · Dec 18, 2024

Abbas818 said:
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 · Dec 18, 2024

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

He answered

Abbas818 said:
It is not possible for me to buy it.

to my answer telling about this library from Fernando.

emexes · Dec 18, 2024

DonManfred said:
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 · Dec 18, 2024

emexes said:
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 · Dec 18, 2024

emexes said:
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 · Dec 18, 2024

Abbas818 said:
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 · Dec 20, 2024

Abbas818 said:
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:

Google App Script:

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 · Dec 21, 2024

personalapps said:

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:

Google App Script:

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 · Dec 22, 2024

Abbas818 said:
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 · Dec 22, 2024

Abbas818 said:
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 · Dec 22, 2024

emexes said:
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 · Dec 23, 2024

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 · Dec 23, 2024

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 · Dec 23, 2024

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 · Dec 24, 2024

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.

B4X:

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 · Dec 24, 2024

Abbas818 said:
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.

Android Question Problem with updating a cell in google sheet

Member

Expert

Member

Expert

Expert

Expert

Expert

Member

Member

Expert

Member

Member

Expert

Member

Attachments

Member

Member

Member

Member

Well-Known Member

Member

Similar Threads

Privacy & Transparency

Privacy & Transparency