Select a single row from google sheets

[kunj thakker]

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"

[ABG]

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]

The word "matches" in your query is NOT going to work :)

Use "contains" instead

https://developers.google.com/chart/interactive/docs/querylanguage#select

[kunj thakker]

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 ?

[TimAI2]

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]

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

[TimAI2]

I will try once more:

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

[TimAI2]

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]

Yes spreadsheet is  public

[kunj thakker]

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);

  // http://stackoverflow.com/a/13410486/1545993

  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]

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

https://github.com/AGetzler/spreadsheet

ABG

[ABG]

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]

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

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:

https://script.google.com/macros/s/AKfycbyp99OV0O2xKVLAq34c-lUmaNml9T0yTZvN7ELSKGryOcu9F6s/exec?query=SELECT B,C,D,E where B contains 'Alana'

AI2 App

BLOCKS

SCREEN

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 thakker]

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]

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]

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

aia file also attached

[kunj thakker]

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

[TimAI2]

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]

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

https://drive.google.com/open?id=1io-RPrI6b6oMzQa5Y9vzD54N3_lww1YG2rCKds8_IjMae8EkN-Tj_9bK Link to Script

.aia is already attached in my previous message

[TimAI2]

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]

https://script.google.com/macros/s/AKfycby6KS8edD-s8XqrJR391eUtYb6ppnrjwVAKzyMgCkXrCnzrv68/exec

the same link i have used in the blocks

https://script.google.com/d/1-Elm-5TvXZT_mO9NtUg_F0dfhoNgVgRSbQDbUf6oouMxYcNwrN2fjz58/edit?usp=sharing Lnk yo ADD DATA Script

I have also attached the .aia file

[TimAI2]

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>);

https://developers.google.com/apps-script/guides/bound

[kunj thakker]

how do i get the Google Sheet file ID?

[TimAI2]

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]

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]

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]

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]

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]

Do I need to deploy my script as web app?

[TimAI2]

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]

https://script.google.com/d/1FiuNCOXIeOyhv0zAoA3r2MaGpuT_GSXChD6rZ8aRlHll7mo_MBZNoT9j/edit?usp=sharing Script

https://docs.google.com/spreadsheets/d/11u0fLx6nVm0lC4vr7Q_yDUctL6gat-FRFfXzN30gVf0/edit?usp=sharing new sheet

I still see the error

https://drive.google.com/open?id=1pVs2Qj0pp3mSx9cBNN1UYzqF4FNSfJ87 the error

[TimAI2]

and your aia ?

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

[TimAI2]

Web App Publishing: ensure that in the "Who has access to the app" box, you select: “Anyone, even anonymous”

[kunj thakker]

Web App Publishing 

by this you mean the one in  the script > publish > deploy as web app ?

[TimAI2]

Yes

[kunj thakker]

Ok I shall try 

[TimAI2]

These instructions are in my original post above:

[kunj thakker]

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

https://docs.google.com/spreadsheets/d/11u0fLx6nVm0lC4vr7Q_yDUctL6gat-FRFfXzN30gVf0/edit?usp=sharing - The spreadsheet

[TimAI2]

Something like this:

(edited put up wrong image before! EDITED again, I'll get this right in a minute!)

[kunj thakker]

still not working

[TimAI2]

I posted the wrong image of blocks (now edited), use these

[kunj thakker]

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]

You need to test before adding to the database

[Michele Miki]

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