Embed an interactive calculator in my website using Google Sheets

Question

I have implemented a calculator in a Google Sheet, and would like to embed it in my website. There will be multiple users. They should be able to enter numbers and see the result, perhaps with a chart, right there in the web page. I do not want to give them full access to the spreadsheet. How do I do this?

Answer 1

A Google Sheet can be embedded in a web site, but all changes by users are saved automatically and right away, and users see each other's changes in real time. Further, in order to enter numbers in cells, the users will need edit access to the spreadsheet. You can embed the spreadsheet so that the menu bar is not visible, but a determined user with edit access will still be able to make a copy of the original if they really want it.

See this web page for an example of what an embedded spreadsheet looks like.

To create a calculator using Google tools, the proper way is to write a web app. The web app can use a Google Sheet to do the calculations in the background, but its user interface will have to be written with HTML and JavaScript. All the code can be part of the same script project that is bound to the Google Sheet.

You may want to take a look at this example:

• Web App - basically a form that lets you enter parameters and show the result
• Google Sheet - all the code is here.

When you click the web app exec link, the doGet() function runs. It is in the script project attached to the spreadsheet:

/**
* Runs automatically when the web app is opened from a link.
*
* @param {Object} e The doGet event parameter.
* @return {HtmlOutput} The demo page.
*/
function doGet(e) {
  // version 1.0, written by --Hyde, 28 January 2020
  const page = HtmlService.createTemplateFromFile('Form.html');
  page.webAppName = 'Web App Demo'; // set the value for the <?= webAppName ?> printing scriplet in Form.html
  page.webAppUrl = ScriptApp.getService().getUrl();
  return page.evaluate();
}

The Form.html file contains a simple HTML form that includes the property onsubmit="onFormSubmit(this)":

    <script>
      function onFormSubmit(formObject) {
        google.script.run.withSuccessHandler(showCalculationResult).processForm(formObject);
      }
      // ...
    </script>
    <form name="<?= webAppName ?>" id="web_app_demo" onsubmit="onFormSubmit(this)">
      <!-- ... -->
    </form>

When the user submits the form, google.script.run is called to execute the processForm(formObject) function on the server side. The function receives form values in formObject, stores these values in a spreadsheet row, waits while the results are calculated using spreadsheet array formulas, and returns the formula result to the form for display to the user:

/**
* Receives the google.script.run call from the HTML page defined in 'Form.html',
* processes the user input sent through the form, and sends the result back to the page.
*
* @param {Object} formObject The |this| variable from the submitted form.
* @return {String} The response to show to the user.
*/
function processForm(formObject) {
  // version 1.0, written by --Hyde, 28 January 2020
  const sheet = SpreadsheetApp.getActive().getSheets()[0];
  const appended = appendRows_(sheet, [new Date(), formObject.inputValue, formObject.checkboxEscape, formObject.selectEscape]);
  // retrieve calculation result from the spreadsheet
  SpreadsheetApp.flush();
  const result = appended.range.offset(0, 6, 1, 1).getValue();
  return result;
}

For more information, see:

• HTML Service: Communicate with Server Functions
• Deploy a script as a web app
• Embedding your web app in Google Sites

To try it out yourself, open the Google Sheet and choose File > Make a copy. To see the code, choose Extensions > Apps Script.