How to Display Google Sheets Cell Data in HTML Dialog

Other Questions Google Sheets
1

I’m working on a project where I need to show data from specific cells in a Google Sheets spreadsheet inside an HTML dialog box. I’ve tried using Google Apps Script to grab the cell values and pass them to the HTML template, but I’m not sure if I’m doing it right. Here’s what I’ve got so far:

function showCellData() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const mainValue = sheet.getRange('B3').getValue();
  const secondaryValue = sheet.getRange('B5').getValue();

  const template = HtmlService.createTemplateFromFile('dialogContent');
  template.mainData = mainValue;
  template.secondaryData = secondaryValue;

  const htmlOutput = template.evaluate().setWidth(400).setHeight(300);
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Cell Data');
}

And for the HTML:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <p>Main data: <?= mainData ?></p>
    <p>Secondary data: <?= secondaryData ?></p>
  </body>
</html>

Can someone help me figure out if this is the right approach? I’m not sure if the data is being passed correctly. Thanks!

2

hey, your code looks good but i’ve got a tip. try using getDisplayValues() instead of getValue(). it’ll give you the formatted values from the sheet. also, make sure your HTML file is named ‘dialogContent.html’. here’s a quick tweak:

const [mainValue, secondaryValue] = sheet.getRange('B3:B5:2').getDisplayValues()[0];

this should grab both values in one go. hope it helps!

3

Your approach is on the right track, but there are a couple of things you can improve. I’ve encountered similar issues when working with Google Sheets and HTML dialogs.

First, make sure your HTML template file is named ‘dialogContent.html’, not just ‘dialogContent’. Google Apps Script looks for the .html extension.

Secondly, for better performance, consider using getDisplayValues() instead of getValue() when fetching cell data. This method returns formatted values as they appear in the sheet.

Lastly, to ensure your data is being passed correctly, you can add a console.log() statement in your script to verify the values before passing them to the template.

Here’s a slightly modified version of your code that should work reliably:

function showCellData() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const [mainValue, secondaryValue] = sheet.getRange('B3:B5').getDisplayValues().flat();

  console.log('Main Value:', mainValue, 'Secondary Value:', secondaryValue);

  const template = HtmlService.createTemplateFromFile('dialogContent.html');
  template.mainData = mainValue;
  template.secondaryData = secondaryValue;

  const htmlOutput = template.evaluate().setWidth(400).setHeight(300);
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Cell Data');
}

Hope this helps! Let me know if you run into any other issues.

4

Your approach is generally correct, but I’d suggest a few tweaks to enhance reliability and efficiency. First, ensure your HTML file is named ‘dialogContent.html’. Second, consider using getDisplayValues() instead of getValue() for formatted data. Here’s an optimized version:

function showCellData() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const [mainValue, , secondaryValue] = sheet.getRange('B3:B5').getDisplayValues()[0];

  const template = HtmlService.createTemplateFromFile('dialogContent.html');
  template.mainData = mainValue;
  template.secondaryData = secondaryValue;

  SpreadsheetApp.getUi().showModalDialog(
    template.evaluate().setWidth(400).setHeight(300),
    'Cell Data'
  );
}

This should work smoothly. If you’re still having issues, double-check your HTML file’s content and make sure it’s properly linked in your project.