Search code examples
javascripthtmlgoogle-sheetsgoogle-apps-scriptspreadsheet

I want to insert the information obtained from the spreadsheet into the dialog using GAS and HTML into the initial value of the text field

It is possible to retrieve information entered into cells in a spreadsheet. Next, how can we insert the information obtained in HTML into a text field as an initial value? I have created it so far, but I am not able to dynamically insert a value into the initial value.

function onRemoveOrEditButton() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const initialValue = sheet.getRange("A1").getValue();
  var htmlOutput = HtmlService.createHtmlOutputFromFile('Dialog').setWidth(300);
  htmlOutput.append('<script>var initialValue = ' + JSON.stringify(initialValue) + ';</script>');
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, ' ');
}

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <link href="https://unpkg.com/@primer/css@^20.2.4/dist/primer.css" rel="stylesheet"/>
    <style>
      .form-control {
        width: 200px;
      }
    </style>
  </head>
  <body>
      <input class="form-control input-block" type="text" id="name" name="name" placeholder="name" style="width: 300px;" value= "<?= initialValue ?>"><br>

  </body>
</html>
Solution

  • Is your expected result as follows?

    Google Apps Script:

    function onRemoveOrEditButton() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const initialValue = sheet.getRange("A1").getValue();
  var html = HtmlService.createTemplateFromFile('Dialog');
  html.initialValue = JSON.stringify(initialValue);
  html.sampleScript = `<script>var initialValue = ${JSON.stringify(initialValue)};console.log(initialValue);</script>`;
  var htmlOutput = html.evaluate().setWidth(300);
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, ' ');
}

    HTML:

    <!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <link href="https://unpkg.com/@primer/css@^20.2.4/dist/primer.css" rel="stylesheet"/>
    <style>
      .form-control {
        width: 200px;
      }
    </style>
  </head>
  <body>
      <input class="form-control input-block" type="text" id="name" name="name" placeholder="name" style="width: 300px;" value= <?!= initialValue ?>><br>
      <?!= sampleScript ?>
  </body>
</html>
    • When you run the function onRemoveOrEditButton, a dialog is opened on the Spreadsheet. The cell value of "A1" of the active sheet is shown in the input tag. Also, you can see the value of initialValue in the console.

    Reference: