javascript - Call a Google Apps Script function in HTML - Stack Overflow

admin管理员组

文章数量:1415684

I coach a sports team and set up a website for it. I would like to add a button to an admin page which I can click to quickly send an email to everyone on the team. This email will say something like: "Today's schedule has changed, please visit the website for more info."

I am sure this could be achieved easier though a distribution list in outlook or something, but I want to use, and get a better understanding of, Google Apps Script.

I have a Google Spreadsheet with the email addresses and a simple script function which sends the email.

This works great when I click to run it inside the script editor, but is there a way to call this from an external website with a button and some JavaScript?

I coach a sports team and set up a website for it. I would like to add a button to an admin page which I can click to quickly send an email to everyone on the team. This email will say something like: "Today's schedule has changed, please visit the website for more info."

I am sure this could be achieved easier though a distribution list in outlook or something, but I want to use, and get a better understanding of, Google Apps Script.

I have a Google Spreadsheet with the email addresses and a simple script function which sends the email.

This works great when I click to run it inside the script editor, but is there a way to call this from an external website with a button and some JavaScript?

• javascript
• google-apps-script
• google-sheets

Share Improve this question Follow edited Jan 14, 2016 at 17:43 Mogsdad 45.8k2121 gold badges163163 silver badges286286 bronze badges asked Dec 13, 2012 at 19:47 AheinleinAheinlein 47222 gold badges77 silver badges1919 bronze badges 2

• Are you using a Google site? Do you want it to run as a gadget on your page? Or would it be a standalone webapp with its own URL? – Serge insas Commented Dec 13, 2012 at 20:26
• 1 it is not a Google site. I guess it would be a standalone web app. I would just like to be able to send a mass email to a list. Before i tried the apps script i looked into storing the email addresses in an XML document stored on the webserver. I was hoping to use JS to loop through the XML and call something like MAILTO: for each one but I was unsuccessful. Is there a better way to do this with javascript? – Aheinlein Commented Dec 13, 2012 at 21:11

Add a ment  | 

3 Answers 3

Sorted by: Reset to default 2

You need to create a Google UI object that you can embed in your site - you'll do this by creating a Script that you will deploy as a web app. (Refer to Google Apps Script - Web Apps.) That object will contain a button that will invoke your existing script function to send the email.

Publish your web app to execute as you. For your specific situation, you will also want to have access to the app limited to you. That will mean that the UI Blob you create will not function for the general public. This is very inplete, but produces a blob with a single button that will trigger your script:

function doGet() {
  var app = UiApp.createApplication();

  var button = app.createButton('Send Schedule Change Email');
  app.add(button);

  var handler = app.createServerHandler('myClickHandler');
  button.addClickHandler(handler);

  return app;
}

function myClickHandler(e) {
  var app = UiApp.getActiveApplication();

  // Call your library function, e.g.
  TeamSpreadsheet.sendScheduleChanged();

  var label = app.createLabel('Message Sent')
                 .setId('statusLabel')
                 .setVisible(false);

  label.setVisible(true);

  app.close();
  return app;
}

Your web app will need to have access to your existing script, which I assume is embedded in your spreadsheet. This is acplished by first saving a version of your existing script (File - Manage Versions), then adding it as a library to your new web app script (Resources - Manage Libraries). Read more about libraries here. Since your web app will run as you, you can keep your spreadsheet private.

Caveats

The utility script in your library will need to open your spreadsheet in a way that will work from outside of the sheet; use SpreadsheetApp.openById(), rather than SpreadsheetApp.getActiveSpreadsheet(). Unfortunately, openById pukes when you use it to open the host spreadsheet, so you'll want something like this:

  var ss = null;
  try {
    // This works for scripts running in the host spreadsheet
    ss = SpreadsheetApp.getActiveSpreadsheet();
  } catch(err) {
    try {
      // This works for web apps
      ss = SpreadsheetApp.openById("SPREADSHEET ID");
    } catch(err) {
      Logger.log("Could not open source spreadsheet.");
      // Well, then, not much sense carrying on, is there?
      return;
    }
  }
  SpreadsheetApp.setActiveSpreadsheet(ss);
  ...

In fact, watch for any reliance on calls to get info from "active" objects. You may need to do some gymnastics to get around them.

I've found debugging this type of arrangement to be painful, since the app-script debugger often reports "Server Errors" when you try to trace into a library routine.

I hope that helps gets you started!

As a plement to Mogsdad's answer (that was quite plete and interesting) I'd say that your case could be a bit simpler since you have already a working script.

Take your script and add a doGet() function like in Mogsdad example, define a handler on the button that will call your existing function you wrote to send mails, in this function replace the getActiveSpreadsheet() by SpreadsheetApp.OpenById("the ID of the SS") and the getActiveSheet() by OpenByName() and you're done with the modifications.

After that follow Mogsdad instructions : deploy the script as a webapp running as you and use the provided url to access it from a link on your site.

If you want not to take any risk, do all these changes on a copy of your original spreadsheet so you always keep a working model at hand. If you want more accurate advice (or if you meet some difficulties) feel free to show your existing script to get some help with the modifications.

PS : please consider this as a simple ment, written in an answer for the fort of formating

Just publish your script as web application and upon a button click, excecute:

window.open("https://script.google./macros/s/<id>/exec");

Don't know if this was possible a year ago.

本文标签： javascriptCall a Google Apps Script function in HTMLStack Overflow