Implement Google Apps Script to HTML

Question

I want to call a Google sheet from within a HTML file. This is possible via documentation (https://developers.google.com/apps-script/guides/html/templates) by:

<body>
    <? var data = SpreadsheetApp
        .openById('1234567890abcdefghijklmnopqrstuvwxyz')
        .getActiveSheet()
        .getDataRange()
        .getValues(); ?>    </body>

That really works and catches the values when I load the HTML File. What I need to do is - I want to set values in a Spreadsheet within a for-loop, which is activated by a button.

function processForm() {

var elementsNr = document.getElementsByName("nummerierung")
var elements = document.getElementsByName("schuelername")
var elementsDate = document.getElementsByName("datuminput")
var elementsArt = document.getElementsByName("art")
var elementsZeit = document.getElementsByName("zeit")

for (var i = 0; i < elements.length; i++) {
    var Nr = elementsNr[i].value;
    var sname = elements[i].value;
    var datum = elementsDate[i].value;
    var Uart = elementsArt[i].value;
    var UZeit = elementsZeit[i].value;

     var data = SpreadsheetApp
                   .openById('1MWy0J5ZKnsXXKWEX72Bh9cRBvsFkDAqTEhW1SxFavmA')
                   .getActiveSheet()
                   .getDataRange(sheet.getLastRow()+1, 1, 1, 5)
                   .getValues([[Nr, datum, sname, Uart, UZeit]]); 
}

But somehow I get an reference Error:

ReferenceError: SpreadsheetApp is not defined

:(

Anyone?

Community · Accepted Answer · 2017-05-23 12:32:52Z

1

The referred documentation is about Templated HTML but the code shown in the question doesn't use the templated HTML syntax. By the other hand, the Best Practices doc suggests to load data asynchronally.

To call a server-side Apps Scripts functions from the client-side JavaScript (scripts on an HTML file), first create a function on the .gs file, then call it from the HTML by using google.script.run

For a code example see the answer by Daniel to SpreadsheetApp.getActiveSpreadsheet() is breaking script

By the way, getDataRange and getValues should not have arguments.

edited May 23, 2017 at 12:32

CommunityBot

11 silver badge

answered Sep 23, 2016 at 12:59

Wicket

39.4k9 gold badges81 silver badges201 bronze badges

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

6 Comments

c_ph_r Over a year ago

Thanks ruben - I tried this before but I had big problems because the functions run asynchronious. :/ In the documentation it looks like this way should work too and it would solve all my problems immediately.

c_ph_r Over a year ago

Thanks again Ruben! Do you see a way to do it in the way I tried, even if it's not -best practice- ? I'm quite desperate. I've tried different ways now: 1. trying to give single values from functionINDEXHTML to functionCODEGS - but because of their different speed some data gets lost. 2. trying to give an array from functionINDEXHTML to functionCODEGS -> doesn't work : stackoverflow.com/questions/39637258/… (Error: Failed due to illegal value in property: 0) 3. trying to implement functionB in functionA (this thread) :'(

Wicket Over a year ago

Please read How to Ask and minimal reproducible example If you need further help on how to ask questions on Stack Overflow, please post a question on Meta Stack Overflow.

Wicket Over a year ago

Added a comment to the answer.

c_ph_r Over a year ago

I've asked a specific problem but the solution you gave me leads to other problems which I illustrated. My question is quite minimal: how can I implement the code of the documentation in my function...

|

Collectives™ on Stack Overflow

Implement Google Apps Script to HTML

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related