0

I am using Google App scripts and am having trouble finding an answer to this fairly simple task: how can I get the data from a Google Sheet in my code.gs and then use it in a published web app (for some visualization w/ D3)?

I would like to get all columns and rows from the spreadsheet, then use those in the data. Preferably they would be in JSON for ease of use, but I can't find the specifics on how to grab all (or any) of the data from a Sheet in my code.gs and then use it in the index.html.

So far I have something like this:

code.gs

function getData(){
 var sheet = SpreadsheetApp.openById("1NCNsdEGAHXWhZMEHnOflsIqElKdQSQxy83iM5vUIa9k");

 //get desired range

 return sheet;
}

index.html

In the <script> in index.html:

 google.script.run.getData();

But am otherwise at a loss. Thanks for any help.

moman822
  • 1,904
  • 3
  • 19
  • 33

1 Answers1

1

Getting the data is fairly easy. However this has to be properly done on both server side and client side.

For code.gs you will need to return actual data, but currently return sheet just returns the sheet object. What you need to do instead is

var vals = sheet.getDataRange().getValues()

which now gets you a 2D array of your sheet values. Now keep in mind that sheet has to be a sheet object not a spreadhseet object. So if your spreadsheet has many sheets, make sure you get the correct one with something like

var sheet = SpreadsheetApp.openById("1NCNsdEGAHXWhZMEHnOflsIqElKdQSQxy83iM5vUIa9k").getSheets()[0];

or

var sheet = SpreadsheetApp.openById("1NCNsdEGAHXWhZMEHnOflsIqElKdQSQxy83iM5vUIa9k").getSheetByName('Sheet1');

Now, since you want to grab the data as a JSON, all you really have to do is 

vals = JSON.stringify(vals)

and now you have a JSON string of values. However that is just half of it, because your index.html that you wrote will not make use of the data as it calls the server side function, but does not listen for a response. In order to do that, you will need to use a success handler as written here. Essentially you will have to call a function in index.html that will make use of the returned vals variable. So something like

google.script.run.withSuccessHandler(useData).getData();

function useData(data) {
   data = JSON.parse(data)
   //now you have a 2D array in your index.html and you can use it as you wish within these client side scripts
}
Vytautas
  • 2,238
  • 1
  • 9
  • 20
  • Thanks for this. Do I need to assign that final `google.script.run...` to a variable in my html? I'm having some difficulty debugging at the moment. – moman822 Apr 05 '17 at 01:27
  • `google.script.run.withSuccessHandler(fooClient).fooServer()` calls `fooClient(ret)` function in your html where the return value of `fooServer()` from your .gs script is passed on as `ret` so you do not need to assign it to any variable (I don't think it's even possible or that it would do anything). You then process the return value inside the `fooClien(ret)` and put it anywhere in your html programatically just like with regular javascript on the webpage. – Vytautas Apr 05 '17 at 04:21
  • Right, I sort of came to that conclusion myself after some testing. Thanks! – moman822 Apr 05 '17 at 12:59