时间戳

首页 > 解决方案 > Google 表格插件中的 HTML 输入总是返回错误的东西

google-apps-script - Google 表格插件中的 HTML 输入总是返回错误的东西

问题描述

我正在为谷歌表格制作一个插件，它修改了numberFormat一些单元格，插入了一个单位测量值（厘米、英寸、英尺、盎司、公斤等），但仍然允许像AVERAGE和SUM正确处理数字这样的公式，忽略文本。然后我添加了一个选项，让您选择是否要在数字和文本之间插入空格，但现在 HTML 输入的value属性总是'on'，即使我写了其他内容或将其留空。

在此处输入图像描述

这是电子表格

代码.gs

const _ss = '_show_sidebar_';

/* What should the add-on do after it is installed */
function onInstall() {
  onOpen();
}

/* What should the add-on do when a document is opened */
function onOpen()
{
  let menu = SpreadsheetApp.getUi()
  .createAddonMenu(); // Add a new option in the Google Docs Add-ons Menu

  menu.addItem("Open sidebar", "showSidebar").addToUi();  // Run the showSidebar function when someone clicks the menu
  menu.addItem("Ignore a value in a formula", "ignoreOnFormula").addToUi();
  menu.addItem("Ignore units in a formula", "ignoreUnitsOnFormula").addToUi();
  menu.addItem("Remove all borders", "removeAllBorders").addToUi();
  menu.addItem("Make a box", "makeBox").addToUi();
}

/* Show a 300px sidebar with the HTML from googlemaps.html */
function showSidebar(sidebar="addon")
{
  let html = HtmlService.createTemplateFromFile(sidebar)
    .evaluate()
    .setTitle("Google Sheets tools"); // The title shows in the sidebar
  SpreadsheetApp.getUi().showSidebar(html);
}

function getSheet()
{
  return SpreadsheetApp.getActive();
}

function formatRange(range, format)
{
  range.setNumberFormats(range.getNumberFormats().map(formats_ => formats_.map(_ => format)));
  return range;
}

function checkIfShouldShowSidebar(value, sidebar)
{
  if (value == _ss)
  {
    showSidebar(sidebar);
    return true;
  }
  return false;
}

function ignoreOnFormula()
{
  let sheet = getSheet();
  let range = sheet.getActiveRange();

  formatRange(range, '(@)');
}

function ignoreUnitsOnFormula(unit=_ss, insertSpace=true)
{
  if (checkIfShouldShowSidebar(unit, 'ignoreUnitsOnFormula')) return;

  let sheet = getSheet();
  let range = sheet.getActiveRange();

  let format = '0.###############';
  if (insertSpace)
  {
    format += ' ';
  }
  format += '"' + unit + '"';

  formatRange(range, format);
}

function setBorders(range, borders, color, style=SpreadsheetApp.BorderStyle.SOLID)
{
  let top        = null;
  let bottom     = null;
  let left       = null;
  let right      = null;
  let vertical   = null;
  let horizontal = null;

  borders = borders.toLowerCase();
  if (borders.includes('t'))
  {
    top = true;
  }
  if (borders.includes('b'))
  {
    bottom = true;
  }
  if (borders.includes('l'))
  {
    left = true;
  }
  if (borders.includes('r'))
  {
    right = true;
  }
  if (borders.includes('v'))
  {
    vertical = true;
  }
  if (borders.includes('h'))
  {
    horizontal = true;
  }

  range.setBorder(top, left, bottom, right, vertical, horizontal, color, style);
}

function removeAllBorders()
{
  let sheet = getSheet();
  let range = sheet.getActiveRange();

  setBorders(range, 'lrtbvh', 'white');
}

function makeBox()
{
  let sheet = getSheet();
  let range = sheet.getActiveRange();

  setBorders(range, 'lrtb', 'black', SpreadsheetApp.BorderStyle.SOLID_THICK);
}

插件.html

<!DOCTYPE html>
<html>
  <head>
    <link href="https://ssl.gstatic.com/docs/script/css/add-ons.css" rel="stylesheet">
    <base target="_top">
  </head>
  <body>
    <div class="sidebar">
      <button
        class="run"
        run="ignoreOnFormula"
        title="This can be helpful when you use a range in a formula (that works with numbers) and there's a specific cell/value you want to ignore."
      >Ignore a value in a formula</button>
      <button
        class="showSidebar"
        id="ignoreUnitsOnFormula"
        title="Insert a unit (cm, in, ft, etc) into a cell, ignoring it in formulas or calculations to avoid errors."
      >Ignore units in a formula</button>
      <button
        class="run"
        run="removeAllBorders"
        title="Make all the borders white."
      >Remove all borders</button>
      <button
        class="run"
        run="makeBox"
        title="Add thick black borders around the selection. This looks good when all other borders have been removed with the Remove All Borders tool."
      >Make a box</button>
    </div>
  </body>
  <style>
    body
    {
      margin: 0px;
    }

    div.sidebar
    {
      display: inline-grid;
    }

    div.sidebar button
    {
      width: 100%;
      margin: 0px 0px 10px 0px;
    }
  </style>
  <script>
    let showSidebarButtons = document.querySelectorAll('button.showSidebar');
    for (let i = 0; i < showSidebarButtons.length; i++)
    {
      showSidebarButtons[i].addEventListener('click', function()
      {
        google.script.run.showSidebar(this.id);
      });
    }

    let runButtons = document.querySelectorAll('button.run');
    for (let i = 0; i < runButtons.length; i++)
    {
      runButtons[i].addEventListener('click', function()
      {
        js = 'google.script.run.' + this.getAttribute('run') + '();';

        eval(js);
      });
    }
  </script>
</html>

忽略UnitsOnFormula.html

<!DOCTYPE html>
<html>
  <head>
    <link href="https://ssl.gstatic.com/docs/script/css/add-ons.css" rel="stylesheet">
    <base target="_top">
  </head>
  <body>
    <div class="sidebar">
      <div id="ignoreUnitsOnFormula">
        <h2>Ignore units in formulas</h2>
        <input
          type="checkbox"
          checked="true"
          id="insertSpace"
        >
        <label for="insertSpace">Insert a space before</label>
        <br>
        <input type="text" placeholder="Enter the unit">
        <button>Apply</button>
        <br>
        <span id="error"></span>
      </div>
    </div>
  </body>
  <style>
    span#error
    {
      color: red;
      font-size: smaller;
    }
  </style>
  <script>
    const _ss = '_show_sidebar_';
    let ignoreUnitsOnFormulaValue  = document.querySelector('#ignoreUnitsOnFormula input');
    let ignoreUnitsOnFormulaButton = document.querySelector('#ignoreUnitsOnFormula button');
    let insertSpaceBeforeCheckbox  = document.querySelector('input#insertSpace');
    let errorSpan = document.querySelector('span#error');

    ignoreUnitsOnFormulaValue .addEventListener('input', function()
    {
      if (this.value == _ss || this.value.trim() == '')
      {
        errorSpan.innerText = 'Invalid value';
        ignoreUnitsOnFormulaButton.disabled = true;
      }
      else
      {
        errorSpan.innerText = '';
        ignoreUnitsOnFormulaButton.disabled = false;
      }
    });
    ignoreUnitsOnFormulaButton.addEventListener('click', function()
    {
      google.script.run.ignoreUnitsOnFormula(
        ignoreUnitsOnFormulaValue.value,
        insertSpaceBeforeCheckbox.checked
      );
    });
  </script>
</html>

标签： google-apps-scriptgoogle-sheetsadd-on

解决方案

推荐阅读