1

I have built a web app to track vehicle usage internal to my organization. One of the features is that the app looks into the existing database (Google Sheets) to check if the Start mileage matches the last End mileage. I do this using Gviz and queries in JavaScript.

Unfortunately, this is done on the client side. Because the potential for a savvy user to view the source code and access the Google Sheet, I would like to move this to the server side (in Google Script). This way, they will only see the results of the query, when they are passed to the client from the Google Script. The sample below is not taken from my Web App, it is what I am playing with to query a Google Sheet from Google Script... I know how to pass values from GS to the client so my main problem is figuring out how to query a Google Sheet from Google Script.

function myFunction() {

    //Google Sheet  
  var sourceSheetString = 'https://docs.google.com/spreadsheets/d/116J**SheetID**w844/gviz/tq?sheet=Sheet1&headers=1&tq=';
  var query = 'select A,B where B = "Brown" order by A desc';
  var queryString = sourceSheetString + query;
 
  var response = UrlFetchApp.fetch(queryString);
  
  var parsedResponse = JSON.parse(response.getContentText());
  
  Logger.log(parsedResponse);
  
}

Unfortunately my Google Apps domain does not allow sharing outside of the domain so I will be unable to share the files, etc.

I have scoured the internet looking for a solution but the closest I could come up with us UrlFetchApp. This is the error I get when I run the function:

SyntaxError: Unexpected token: < (line 10 (var parsedResponse), file "Code")

When I paste the complete url string into the browser window it works fine so I know it is not a problem with the query string itself.

@SandyGood when I log the response I get this: <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <meta content="width=300, initial-scale=1" name="viewport"> <meta name="description" content="Create a new spreadsheet and edit with others at the same time -- from your computer, phone or tablet. Get stuff done with or without an internet connection. Use Sheets to edit Excel files. Free from Google."> <meta name="google-site-verification" content="LrdT**QXZs"> <title>Google Sheets - create and edit spreadsheets online, for free.</title> <style> a bunch of CSS...

Rubén
  • 34,714
  • 9
  • 70
  • 166
MSG Jeff Crane
  • 79
  • 3
  • 13
  • It sounds like maybe you are trying to set up an Execution API. Google has a tutorial with templates in several languages: [Create a Target Project for the Apps Script Execution API](https://developers.google.com/apps-script/guides/rest/quickstart/target-script) – BMcV Jan 17 '16 at 17:03
  • @BMcV are you suggesting that I use the Execution API to run a stand alone Apps Script project that runs the queries and then pull the query results in? – MSG Jeff Crane Jan 17 '16 at 18:54
  • 1
    I'm unsure of where your app exists. Is it deployed as a [Web App](https://developers.google.com/apps-script/guides/web?hl=en)? Or does it exist on another domain? Where is the Google Script located? Is it a standalone file? Or is it accessed in the spreadsheet script editor? – BMcV Jan 17 '16 at 19:24
  • 2
    Is the HTTP Response coming from the query valid JSON? If it isn't, then parsing it would probably create an error. Log the response, then test it for being valid JSON. `Logger.log(response.getContentText());` You can do a web search for sites that test for valid JSON. – Alan Wells Jan 17 '16 at 19:33
  • See this page https://developers.google.com/chart/interactive/docs/spreadsheets#gid I would follow the example and stop at `var data = response.getDataTable();` and then do what you want with the data. You'll have to load the Visualization API – User Jan 18 '16 at 10:10
  • @BMcV it is deployed from a stand alone Google Script file that uses HTMLService to create the interface/JavaScript. It is not technically deployed as a web app, users used the published version link to access it. – MSG Jeff Crane Jan 18 '16 at 16:40
  • @User this is how I am using it today. Unfortunately it is client-side JavaScript which includes the URL to the Google Sheet database (the query string). If a user wanted to, they could paste the URL into their browser and access the entire database. I want to avoid this by not exposing the query string on the client side, instead querying on the server side and then passing only the query results to the user. – MSG Jeff Crane Jan 18 '16 at 16:45
  • 1
    Related question: [Can I use Google Visualization API to query a spreadsheet in apps script?](http://stackoverflow.com/questions/22330542/can-i-use-google-visualization-api-to-query-a-spreadsheet-in-apps-script) – User Jan 18 '16 at 17:25

1 Answers1

0

As @SandyGood mentioned, the error is most likely a JSON parsing error. Look at the raw response from your var response = UrlFetchApp.fetch(queryString); line.

My guess is it will include something like

/*O_o*/ google.visualization.Query.setResponse(

followed by json data and then a trailing );

Remove those extraneous parts of the response first and then parse the JSON. So:

var response = UrlFetchApp.fetch(queryString);
var cleanedResponse = yourCleanFunction(response.getContentText());
var parsedResponse = JSON.parse(cleanedResponse);

Where yourCleanFunction() is a function you write that does the necessary stripping.

User
  • 62,498
  • 72
  • 186
  • 247
  • Thank you everyone, have not had a chance to try the solution from @Sandy-Good but I agree, that may be it. Will report back with the findings... – MSG Jeff Crane Jan 18 '16 at 17:49
  • @SandyGood when I log the response I get this: ` Google Sheets - create and edit spreadsheets online, for free. – MSG Jeff Crane Jan 18 '16 at 19:12
  • Based on the logged response, URLFetchApp is not behaving the same way as when I simply enter the query string into the browser bar and press enter... – MSG Jeff Crane Jan 18 '16 at 19:16
  • Wanted to say thanks for everyones help, I was never able to get this to work but I did figure out a workaround where I build the query in another Google Sheet and give access to that limited dataset. – MSG Jeff Crane Jan 21 '16 at 16:59