Select a single row from google sheets

1458 views
Skip to first unread message

kunj thakker

unread,
Jul 2, 2019, 6:53:18 AM7/2/19
to MIT App Inventor Forum
I want To select a single row from google sheets
as of now I used
select C,D,E where B matches 'value'
but instead of 1 value from column it returns all the values from the column
my current condition is
select C,D,E where B matches 'kunj'
 All I want Is Value Agaist "kunj"

blocks.png
blocks (1).png
Screenshot_2019-07-02-16-10-30.png
Untitled.png

ABG

unread,
Jul 2, 2019, 9:10:36 AM7/2/19
to MIT App Inventor Forum
More information is needed on the URL.
It contains keywords that control the format, (JSOn vs csv)
but the AI2 text block is hiding them at the end.

If you break the URL up into smaller pieces and JOIN them
in the global initialize of url, they will be visible.

Also, show us the blocks that parse and display the results when they arrive.

And for good measure, show us a Do It of the JOIN value going into Web1.URL.

ABG


TimAI2

unread,
Jul 2, 2019, 6:33:47 PM7/2/19
to MIT App Inventor Forum
The word "matches" in your query is NOT going to work :)

Use "contains" instead

kunj thakker

unread,
Jul 10, 2019, 2:07:49 PM7/10/19
to mitappinv...@googlegroups.com
ABG I am attaching  the .aia file and Tim I want the feild to match exactly as I am creating  a login  app so login ID should be unique so will  it work ?
Ddr (1).aia

TimAI2

unread,
Jul 10, 2019, 4:12:37 PM7/10/19
to MIT App Inventor Forum
Based upon your current query, The items in Column B will need to be unique in order to return only one item. You need to changes 'matches' to 'contains'

You can make users enter a unique name (Column B) by testing their entry against what is already there (use the "is in list" block for this)

ABG

unread,
Jul 10, 2019, 5:38:22 PM7/10/19
to MIT App Inventor Forum
The response from the SQL Web request is not coming back
as a csv table.

It's JSon-like, not not JSON.

Does any one know a keyword that can be added to the URL
to get csv results, while preserving the SQL functionality?

ABG

Capture.PNG
blocks.png
search_query.PNG
Ddr_ABG.aia

TimAI2

unread,
Jul 10, 2019, 7:13:47 PM7/10/19
to MIT App Inventor Forum
I will try once more:

SELECT C,D,E WHERE B CONTAINS 'Textbox.Text'

TimAI2

unread,
Jul 10, 2019, 8:47:58 PM7/10/19
to MIT App Inventor Forum
The other issue here is that the spreadsheet has to be public in order to use the gviz query. Depends how secure the login data has to be....

kunj thakker

unread,
Jul 12, 2019, 1:03:54 PM7/12/19
to MIT App Inventor Forum
Yes spreadsheet is  public

kunj thakker

unread,
Jul 12, 2019, 1:14:11 PM7/12/19
to MIT App Inventor Forum
function action(e) {
  var key    = e.values[1]; // the entered name, column 2
  var action = e.values[4]; // the entered action (UPDATE or DELETE), column 5
  var sheet  = SpreadsheetApp.getActiveSheet();
  var values = sheet.getDataRange().getValues();
  Logger.clear();
  Logger.log('action=' + action);

  if (action == "DELETE"){
    del(key, sheet, values);
  }
  if (action == "UPDATE") {
    //upd(key, sheet, values);
    upd2(key, 1, sheet, values);
  }
}

function del(key, sheet, values){
  Logger.log('DELETE ' + key);
  for(var row = values.length -1; row >= 0; --row){
    if (values[row][1] == key){
      sheet.deleteRow(parseInt(row)+1); // loop is 0-indexed, deleteRow is 1-indexed
    }
  } // end: for
}

// Version 1, deprecated
function upd(key, sheet, values){
  var lastRow = sheet.getLastRow();
  for(i in values){
    if (values[i][1] == key){
      Logger.log('UPDATE i=' + i);
      var rangeToCopy = sheet.getRange(lastRow, 1, 1, 4); // getRange(row, column, numRows, numColumns), do not copy action column
      rangeToCopy.copyTo(sheet.getRange(parseInt(i)+1, 1));
    }
  } // end: for
  sheet.deleteRow(lastRow); // delete last row: this is the update statement
}

// Version 2, author: Charles
function upd2(key, key_column, sheet, values){
  var lastRow = values.length - 1;

  // Remove 'UPDATE' from column 5 (ACTION column)
  sheet.getRange(parseInt(lastRow)+1, 5).clear();

  // Remove entries with same key.
  for(var row = lastRow - 1; row >= 0; --row){
    if (values[row][key_column] == key){
      sheet.deleteRow(parseInt(row)+1); // loop is 0-indexed, deleteRow is 1-indexed
    }
  } // end: for
}  
Is there any issue with the script ?
This is the current script of the spreadsheet
I have taken this from Taifun's website

ABG

unread,
Jul 12, 2019, 1:38:09 PM7/12/19
to MIT App Inventor Forum
I can't speak to the scripting,
it's too far afield from blocks for my taste.

However, I got a personal table to respond
to Taifun's URL SELECT query pattern.

I got it to work by using the Sheets
File->Publish dialog with 
further specification that I wanted to publish Sheet1 as csv.
(See attached.)

For draggable blocks .png files, see my new github repository for this post ...

ABG


ShareSettings1.PNG
TestData.PNG
strSelectURL.png
btnSelect.png
Taifun test data - Sheet1.csv
SELECT results.PNG
File_Publish_Sheet1_csv.PNG

ABG

unread,
Jul 12, 2019, 1:58:28 PM7/12/19
to MIT App Inventor Forum
Your test data is too mushed for me to confirm that it matches your
query by column, for example is
kunj in the column you mention in the WHERE clause?

Also, check your Google Sheets File->Publish
details if there's more to say, like in my example.

ABG

TimAI2

unread,
Jul 13, 2019, 5:43:34 AM7/13/19
to MIT App Inventor Forum
Kunj

You seem to be getting in a muddle, and mixing up different approaches to accessing the data on the google sheet.
Given your original request, which was to "select a single row from google sheets", could I suggest the following method:

1. Will work with a private google sheet - I have left my example public so you can see what is happening
2. Requires a script bound to the google sheet that works as a web app to capture the query and return the query data
3. Allows construction of any query of the data from within the app (hard coded or to be typed in)

Google Sheet: link to spreadsheet

You will require three tabs / sheets in your google spreadsheet, one for the data, one for the query and one for the output

sheet_data.png

sheet_query.png

sheet_output.png


The Output tab holds the complete query formula and generates the output based upon the query supplied to the Query tab by the web app


WebApp


The web app simply supplies the query written in the AI2 app as a parameter and pastes it to the Query tab, which then generates the output

The web app grabs the query output and returns it to the AI2 app as responseContent


//for testing with PC browser
function doGet(e) { 
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('QUERY');
  var sh = ss.getSheetByName('OUTPUT');
  var query = e.parameter.query ; 
  sheet.clearContents();
  sheet.appendRow([query]);
  var output = sh.getDataRange().getDisplayValues();
  return ContentService.createTextOutput(output);
}
//for use with AI2
function doPost(e) { 
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('QUERY');
  var sh = ss.getSheetByName('OUTPUT');
  var query = e.parameter.query ; 
  sheet.clearContents();
  sheet.appendRow([query]);
  var output = sh.getDataRange().getDisplayValues();
  return ContentService.createTextOutput(output);
}


You need the doGet (for testing with a PC browser) and the doPost (for use with the AI2 app)

Make sure you publish the script as a web app, so that it runs as you (the owner of the google sheet) but to be accessed by anyone, even anonymous, and update to a new version and re-publish every time you make a change to the script, if you need to...


to test using your PC browser use a url like this:





AI2 App


BLOCKS


blocks_query.png




SCREEN

screenqueryoutput.png


That is it.


You will need to ensure that any typed or hard coded queries follow the google query language syntax: https://developers.google.com/chart/interactive/docs/querylanguage


Now I know you will have a supplementary question: how do I update a value? 

That is considerably more complex, but if you continue to use google apps script, then I would refer you to my HOWTO on that! 


kunj_data.aia

kunj thakker

unread,
Jul 14, 2019, 2:42:26 AM7/14/19
to mitappinv...@googlegroups.com
Hey Tim do I keep The current script in my application or just add your script below it?
but will it work for many users logging in at same time?

Thank you Tim

TimAI2

unread,
Jul 14, 2019, 5:10:27 AM7/14/19
to MIT App Inventor Forum
The web app I supplied above should be in its own project file.
To create a new project file bound to your sheet, open your current project, the File > New > Project > Save
Next time you click on "Script editor" in your sheet you will be offered a choice of projects.

That said, if you use the above method, and that is all you are doing, there is no need for any other scripts....

No reason why the web app will not work for multiple users, I have not built in a lock to the the webapp, and there is no built in concurrency, but the chances of two users calling it at exactly the same time are minimal, and it only takes a second to run

TimAI2

unread,
Jul 20, 2019, 4:24:11 PM7/20/19
to mitappinv...@googlegroups.com
Going on from above, I indicated you may wish to edit the records, i have therefore prepared an example of how to do this using much the same method.

Again we let the google sheet do some of the work, using another query. I felt it was important to know not only the unique value in each row, but the number of the row, this would allow for records to be added or deleted, and still edit the correct date. I therefore added an array formula to the base data to allow for this. When new rows are added, the row number is automatically listed. The new query looks for the unique name, and returns the row number, this is collected by the google web app later.

We create a new bound project file for another web app. We send our edited data, along with the rest of the row data to the web app. I have added a five second lock to the web app to prevent any concurrent activity. The web app checks the unique name, sends it to the query and fetches the row number. Once it has this, it can then go ahead and post the new data to the correct row, and return "Success" to the app.

The app example uses three web components: one to call the unique names from the sheet, one to return the row data related to the chosen unique name, and the third, to carry out the update. The short Video attached shows how it works.

WEB APP


//for testing with PC browser
function doGet(e) { 
  
  //lock sheet to prevent concurrent changes  
  var lock = LockService.getPublicLock();
  lock.waitLock(5000);
  
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('KUNJ_DATA');
  var sh = ss.getSheetByName('ROWQUERY');
  var sht = ss.getSheetByName('ROWOUTPUT');
  sh.getRange("A3").setValue(e.parameter.name);
  var data = [ [ e.parameter.timestamp, e.parameter.name, e.parameter.id, e.parameter.pass, e.parameter.email, e.parameter.ac  ] ]; 
  var row = sht.getRange("A1").getDisplayValue();
  sheet.getRange("A"+parseInt(row)+":F"+parseInt(row)).setValues(data);
  
  return ContentService.createTextOutput("Success");
  
 //release spreadsheet for editing
 lock.releaseLock();
}





//for use with AI2
function doPost(e) { 
  
  //lock sheet to prevent concurrent changes  
  var lock = LockService.getPublicLock();
  lock.waitLock(5000);
  
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('KUNJ_DATA');
  var sh = ss.getSheetByName('ROWQUERY');
  var sht = ss.getSheetByName('ROWOUTPUT');
  sh.getRange("A3").setValue(e.parameter.name);
  var data = [ [ e.parameter.timestamp, e.parameter.name, e.parameter.id, e.parameter.pass, e.parameter.email, e.parameter.ac  ] ]; 
  var row = sht.getRange("A1").getDisplayValue();
  sheet.getRange("A"+parseInt(row)+":F"+parseInt(row)).setValues(data);
  
  return ContentService.createTextOutput("Success");
  
 //release spreadsheet for editing
 lock.releaseLock();
}



BLOCKS

blocks-update-google-sheet-row.png


aia file also attached



kunj_data_edit.aia
update.webm

kunj thakker

unread,
Jul 21, 2019, 12:49:38 PM7/21/19
to MIT App Inventor Forum
Hey Tim I saw Your Eg and all I did That was I changed the URL but I guess something went wrong

I have attached Aia file and the result I am getting
kunj_data (1).aia
Screenshot_2019-07-21-22-07-08-370.jpeg

TimAI2

unread,
Jul 21, 2019, 1:43:11 PM7/21/19
to MIT App Inventor Forum
Nothing wrong with your blocks, check your web app, ensure you have saved/published the latest version, and that anyone, even anonymous can access.

When working with google drive, I need to be able to see your aia, your web app script and your data in order to test 

kunj thakker

unread,
Jul 22, 2019, 4:16:41 AM7/22/19
to MIT App Inventor Forum

TimAI2

unread,
Jul 22, 2019, 4:51:56 AM7/22/19
to MIT App Inventor Forum
The link you sent for your script project "DATA" is not bound to the spreadsheet, however "ADD DATA" is.

Are you using the script / web app url  ending in exec for ADD DATA in your blocks ?

kunj thakker

unread,
Jul 24, 2019, 3:10:50 AM7/24/19
to MIT App Inventor Forum
kunj_ADD_DATA__SCRIPT.aia

TimAI2

unread,
Jul 24, 2019, 3:42:58 AM7/24/19
to MIT App Inventor Forum
If your web app project file is not bound to the spreadsheet, then some of the script will not work:

SpreadsheetApp.getActive();   <<< only works in bound scripts/proejects

You will need to change this to

SpreadsheetApp.openById(<GOOGLE SHEET FILE ID HERE>);
or
SpreadsheetApp.openByUrl(<URL TO GOOGLE SHEET, INCLUDING GID>);

kunj thakker

unread,
Jul 24, 2019, 10:54:21 AM7/24/19
to mitappinv...@googlegroups.com
how do i get the Google Sheet file ID?

TimAI2

unread,
Jul 24, 2019, 11:15:00 AM7/24/19
to MIT App Inventor Forum
You put up the url in an earlier post:

it is this part:

https://docs.google.com/spreadsheets/d/1CIC48yKDb4pMIyZOvoA7thpDpa7G39Nc11CLOCXMyNA/edit?usp=sharing

and you make the same selection between the "/d/" and the "/edit?" to get the ID of any google sheet (same applies to other "google" documents...)

kunj thakker

unread,
Aug 8, 2019, 12:58:51 PM8/8/19
to MIT App Inventor Forum
Hey Tim 
I am still getting the same error, I guess the problem is with my QUERY sheet as it contains many cells and not just one
so could you tell me how you created your QUERY sheet with only one cell in it
the error is same as inScreenshot_2019-07-21-22-07-08-370.jpeg

TimAI2

unread,
Aug 8, 2019, 1:16:56 PM8/8/19
to mitappinv...@googlegroups.com
Unless I misunderstand, it is all there in the first example i provided further up in this thread ?

If you are still getting that error then your query (full url) is malformed, or you are calling the wrong spreadsheet, or you have not shared the spreadsheet you are currently using, or your web app is malformed or not republished, or something else.
We have been over all of this before.... ;)

Please explain further...

kunj thakker

unread,
Aug 12, 2019, 11:04:32 AM8/12/19
to mitappinv...@googlegroups.com
var query = e.parameter.query ; 
  sheet.clearContents();
  sheet.appendRow([query]);

Tim Tell me how do I enter var query into a particular row and column
And also tell me what do you mean by "doPost (for use with the AI2 app)"

TimAI2

unread,
Aug 12, 2019, 11:21:14 AM8/12/19
to MIT App Inventor Forum
That is a google apps script question!

Use:

var rng = sheet.getRange("<Cell Reference>");
rng
.setValue(query);

(where <Cell Reference> looks like A7 or B4  - you need the quotes around the A1Notation)

kunj thakker

unread,
Aug 12, 2019, 11:36:31 AM8/12/19
to MIT App Inventor Forum
Do I need to deploy my script as web app?

TimAI2

unread,
Aug 12, 2019, 11:47:41 AM8/12/19
to MIT App Inventor Forum
Looks like you are using a web app already, the code I posted needs to go in it to replace the appendRow line

kunj thakker

unread,
Aug 12, 2019, 12:31:32 PM8/12/19
to mitappinv...@googlegroups.com
kunj_data (2).aia

TimAI2

unread,
Aug 12, 2019, 12:36:06 PM8/12/19
to MIT App Inventor Forum
and your aia ?

Assuming you have correctly published your web app with a new version, spreadsheet and web app look OK

TimAI2

unread,
Aug 12, 2019, 12:38:23 PM8/12/19
to MIT App Inventor Forum
Web App Publishing: ensure that in the "Who has access to the app" box, you select: “Anyone, even anonymous”

kunj thakker

unread,
Aug 12, 2019, 12:48:45 PM8/12/19
to MIT App Inventor Forum
Web App Publishing 
by this you mean the one in  the script > publish > deploy as web app ?

TimAI2

unread,
Aug 12, 2019, 12:51:20 PM8/12/19
to MIT App Inventor Forum
Yes

kunj thakker

unread,
Aug 12, 2019, 12:53:04 PM8/12/19
to MIT App Inventor Forum
Ok I shall try 

TimAI2

unread,
Aug 12, 2019, 12:55:23 PM8/12/19
to MIT App Inventor Forum
These instructions are in my original post above:

kunj thakker

unread,
Aug 14, 2019, 8:03:37 AM8/14/19
to mitappinv...@googlegroups.com
Hey Tim 
what I want is each and every Id should be unique (which is to be entered by user ) so I do not want repetition.
Whatever I have tried so far is in the .aia file
Ddr (2).aia

TimAI2

unread,
Aug 14, 2019, 9:02:26 AM8/14/19
to mitappinv...@googlegroups.com
Something like this:
(edited put up wrong image before! EDITED again, I'll get this right in a minute!)



blocksuniqueIDs.png


kunj thakker

unread,
Aug 14, 2019, 9:28:42 AM8/14/19
to MIT App Inventor Forum
still not working
Ddr (3).aia

TimAI2

unread,
Aug 14, 2019, 9:31:33 AM8/14/19
to MIT App Inventor Forum
I posted the wrong image of blocks (now edited), use these


blocksuniqueIDs.png

kunj thakker

unread,
Aug 14, 2019, 9:52:49 AM8/14/19
to MIT App Inventor Forum
you didn't get me 
after user inputs the name/Id the Id is taken as query to sheets , if the name/id already exists in the database , It will ask the user to enter a different one , which would be used for his account login in future

TimAI2

unread,
Aug 14, 2019, 10:49:31 AM8/14/19
to MIT App Inventor Forum
You need to test before adding to the database

Michele Miki

unread,
Dec 17, 2019, 12:51:33 PM12/17/19
to mitappinv...@googlegroups.com

TimAI2 you can help me :( I would like to edit the data in the Name and Nic column, You are a great I am not capable. If you can help me Thanks
Bloc.jpg
Designer.jpg
Script.txt
SpreadSheet.jpg
Dati_SpreadSheet.aia
Reply all
Reply to author
Forward
This conversation is locked
You cannot reply and perform actions on locked conversations.
0 new messages