How to get Spreadsheet Data into clientside array via HTMLService (Google App Scripts)

Question

I am using GAS HTMLService to design a Web App and have hit a major hurdle that I can't seem to overcome -- so I'm hoping to get some help from y'all.

I'm able to create the template and display the HTML properly using the standard doGet:

function doGet(e){
var ss = SpreadsheetApp.openById('SpreadSheetKey');
var usersheet = ss.getSheetByName('UserInfo');
var lastRow = usersheet.getLastRow();
var Userrange = usersheet.getRange(2, 1, lastRow-1, 4);
var Uservalues = Userrange.getValues();
var length = Uservalues.length;

var template = HtmlService.createTemplateFromFile('Form.html');
template.stuff = {};
template.stuff = Uservalues;

template.action = ScriptApp.getService().getUrl();

return template.evaluate();
}

The HTML will render up fine so long as I remove this piece of code from my HTML file:

  $('#chkP').click(function(){
var email = '<?=email?>';
var usename = $('#myuserName').val();

for(var i=0;i<num;i++){
var name = '<?!=stuff[i][0]?>';
alert(name);
}    
});

The issue appears to be related to trying to use the 'i' variable -- I can get the proper info using:

'<?=stuff[2][0]?>' // THIS WORKS

<?=stuff[i][0]?> //THIS DOES NOT -- IT IS ALWAYS undefined 
// it's like 'i' and <?stuff?> aren't in the same scope??

so I have the theory that I have a ServerSide array:

 (Spreadsheetapp.blahblah.getValues();)

that is not available for the Client Side to play with.

My question to you folks is how do I get this data from the Spreadsheet into the HTML form and available to iterate through as an array using a simple 'for' loop....

OR is there a better way?

Corey G · Accepted Answer · 2013-05-09 05:33:09Z

5

You are correct that the array is on the server. Any code inside <? .. ?> is server code) and not available on the client.

What you can do is copy the entire array to the client, so that you can iterate through it later:

Suppose you had an array on the server:

<? var serverStuff = [1, 2, 3]; ?>

You can tell the template to copy it over to the client (basically, write the values of it directly into the client code.

<script>
    ...
    var clientStuff = <?!= JSON.stringify(serverStuff) ?>;
    ...

    // Now you have it on the client! You can use it here:
    alert(clientStuff[0]);
</script>

You can see what is happening by logging the content of the template (after evaluating) on the server:

Logger.log(template.evaluate().getContent());

Which would give you this actual client code, with the copy of the array written into it:

<script>
    ...
    var clientStuff = [1, 2, 3];
    ...

    // Now you have it on the client! You can use it here:
    alert(clientStuff[0]);
</script>

answered May 9, 2013 at 5:33

Corey G

7,9021 gold badge30 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2348688 Over a year ago

Thank you, Corey! This works perfectly...you saved me a TON of frustrations and headaches. Thanks again!!!

Collectives™ on Stack Overflow

How to get Spreadsheet Data into clientside array via HTMLService (Google App Scripts)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related