Retrieve data from a cell in a dialog box

In a previous tutorial, you saw how to create a sidebar (or a dialog box) and insert data into cells.

Here, we will see the reverse operation, that is to say, retrieve data from a cell to display it in a dialog box or a sidebar.

You will see that the process is quite similar because you will also use google.script.run to execute the desired function.

Simple dialog box

To begin, here are the elements to create this simple dialog box:

google apps script custom window retrieve data from cell

The Apps Script code:

function example() {
  const html = HtmlService.createHtmlOutputFromFile('Dialog-box')
    .setWidth(300)
    .setHeight(50);
  SpreadsheetApp.getUi().showModalDialog(html, 'Example');
}

And the HTML code:

<!DOCTYPE html>
<html>
  <head>
    <style>
      p {
        font-size: 1.2rem;
      }
    </style>
  </head>
  <body>
    <p id="example">Site = ?</p>
  </body>
</html>

If this is not clear to you, see the tutorial on creating a dialog box.

Retrieve data from a cell

To retrieve the data from cell A1 and display it in the dialog box, start by creating an Apps Script function that will return this information:

function getA1() {
  return SpreadsheetApp.getActiveSheet().getRange('A1').getValue();
}

You can then call this function like this, by adding for example this JavaScript code before the end of the body tag in your HTML code:

google.script.run.withSuccessHandler(a1Value => {
  document.getElementById('example').textContent = 'Site = ' + a1Value;
}).getA1();

The dialog box will then display this:

google apps script custom dialog box retrieve data from cell

And to better understand, here is this JavaScript code snippet inserted in the HTML code:

<!DOCTYPE html>
<html>
  <head>
    <style>
      p {
        font-size: 1.2rem;
      }
    </style>
  </head>
  <body>
    <p id="example">Site = ?</p>
    <script>
      google.script.run.withSuccessHandler(a1Value => {
        document.getElementById('example').textContent = 'Site = ' + a1Value;
      }).getA1();
    </script>
  </body>
</html>

Display a different result in case of error

In this example, the information is displayed only if the function is executed correctly (hence its name withSuccessHandler).

To display different information in case of error, add withFailureHandler:

google.script.run.withSuccessHandler(a1Value => {
  document.getElementById('example').textContent = 'Site = ' + a1Value;
}).withFailureHandler(() => {
  document.getElementById('example').textContent = 'ERROR';
}).getA1();

Function

The withSuccessHandler and withFailureHandler methods execute the specified function in case of success or failure.

In the initial example, an arrow function was directly entered into withSuccessHandler:

google.script.run.withSuccessHandler(a1Value => {
  document.getElementById('example').textContent = 'Site = ' + a1Value;
}).getA1();

Note that you can also create a specific function and enter the function name in withSuccessHandler if you prefer:

function modifyText(a1Value) {
  document.getElementById('example').textContent = 'Site = ' + a1Value;
}
google.script.run.withSuccessHandler(modifyText).getA1();

Simply execute a function

Sometimes, it is not necessary to know if the function was successfully executed... If that is the case and if the function does not return any data you need to retrieve, you can remove withSuccessHandler and withFailureHandler to simply execute the function:

google.script.run.myFunction();