user8617716
user8617716

Reputation:

How to use regex URL to query a Google Spreadsheet

The following queries column A in a published Google Spreadsheet. The result is all rows with text comment in them.

var word = "comment";    
var id = "unBCxSc8hR41dg6s6N3d17uccj8jnK5Xsn68C58Y76r9";
var url = "https://docs.google.com/spreadsheets/d/"+id+"/gviz/tq?tq=SELECT%20*%20where%20A%20contains%20%22"+word+"%22";

Please tell me what changes should I make to the URL in order to use regex.

For example, I need to find all rows with pattern co..en.* or .*ment in them.

~·~·~·~·~·~·~·~·~·~·~·~·~·~·~·~·~·~·~·~

EDIT

After Tanaike's help:

var word = "f.*t";
var columna = "A";
var id = "4tz810VLT4qv7Q9t94p24tz810VLT4qv7Q9t94p24tz8";
var url = "https://docs.google.com/spreadsheets/d/"+id+"/gviz/tq?tq=select%20"+columna+"%20where%20A%20matches%20%27"+word+"%27";

This will match fit, faint, font, fruit, feet, fat, etc.

~ finis ~

Upvotes: 1

Views: 364

Answers (1)

Tanaike
Tanaike

Reputation: 201378

  • You want to retrieve the values using the regex of co..en.* or .*ment.
  • You want to achieve this using the query language of Google Visualization API.

If my understanding is correct, how about this answer? Please think of this as just on of several possible answers.

In this case, how about using matches? The modified query is as follows.

Modified query:

select * where A matches 'co..en.*|.*ment'

In this case, in order to use this query, please encode it with the URL encode. So when your endpoint is modified, please modify as follows.

From:

tq=SELECT%20*%20where%20A%20contains%20%22"+word+"%22"

To:

tq=select%20%2A%20where%20A%20matches%20%27co..en.%2A%7C.%2Ament%27

Reference:

If I misunderstood your question and this was not the result you want, I apologize.

Upvotes: 1

Related Questions