清理 Google 表格电子表格中的数据

编码水平：中级
时长：20 分钟
项目类型：编辑器插件

目标

• 了解解决方案的功能。
• 了解 Apps 脚本服务在解决方案中的作用。
• 设置脚本。
• 运行脚本。

关于此解决方案

通过以下方式清理电子表格数据：自动移除空行和空列、将电子表格裁剪到数据范围的边缘，以及填补数据中的空白。

运作方式

该脚本可执行以下功能：

• 删除空白行 - 在所选范围内，脚本会识别空白行并将其删除。如果某行中的单元格包含空格字符，则该行不被视为空行。
• 删除空白列 - 在所选范围内，脚本会识别空白列并将其删除。如果列中的单元格包含空格字符，则该列不被视为空列。
• 将工作表裁剪为数据范围 - 脚本会确定数据范围的结束位置，并删除多余的行和列。
• 填充空白行 - 脚本会将所选有效单元格的内容复制并粘贴到其下方行中的空白单元格。当脚本遇到非空行或到达数据范围的末尾时，会停止粘贴内容。

Apps 脚本服务

此解决方案使用以下服务：

• Spreadsheet Service - 获取活动工作表并执行所有清理功能。

前提条件

如需使用此示例，您需要满足以下前提条件：

• Google 账号（Google Workspace 账号可能需要管理员批准）。
• 可访问互联网的网络浏览器。

设置脚本

1. 点击以下按钮，打开 Clean sheet Apps 脚本项目。
   打开项目
2. 点击概览图标 。
3. 在概览页面上，点击“复制”图标 。
4. 在复制的项目顶部，依次点击部署 > 测试部署。
5. 点击选择类型旁边的“启用部署类型”图标 > 编辑器插件。
6. 点击创建新的测试。
7. 在测试文档下，点击未选择任何文档。
8. 选择包含要清理的数据的电子表格，然后点击插入。如需使用示例文档，请复制“清理数据示例”电子表格。
9. 点击保存测试。
10. 如需打开电子表格，请选择已保存的测试旁边的单选按钮，然后点击执行。

运行脚本

1. 在电子表格中，选择范围 A1:F20。
2. 依次点击扩展程序 > Clean sheet 的副本 > 删除空白行。
3. 出现提示时，点击继续，然后为脚本授权。
4. 依次点击扩展程序 > Clean Sheet 的副本 > 删除空白行。
5. 依次点击扩展程序 > Clean Sheet 的副本 > 删除空白列。
6. 依次点击扩展程序 > Clean Sheet 副本 > 将工作表裁剪为数据范围。
7. 选择单元格 C7。
8. 依次点击扩展程序 > “Clean sheet”的副本 > 填写下面的空白行。

查看代码

如需查看此解决方案的 Apps 脚本代码，请点击下方的查看源代码：

查看源代码

Code.gs

solutions/editor-add-on/clean-sheet/Code.js

在 GitHub 上查看

// To learn how to use this script, refer to the documentation:
// https://developers.google.com/apps-script/add-ons/clean-sheet

/*
Copyright 2022 Google LLC

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

    https://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/

// Application Constants
const APP_TITLE = "Clean sheet";

/**
 * Identifies and deletes empty rows in selected range of active sheet.
 *
 * Cells that contain space characters are treated as non-empty.
 * The entire row, including the cells outside of the selected range,
 * must be empty to be deleted.
 *
 * Called from menu option.
 */
function deleteEmptyRows() {
  const sheet = SpreadsheetApp.getActiveSheet();

  // Gets active selection and dimensions.
  const activeRange = sheet.getActiveRange();
  const rowCount = activeRange.getHeight();
  const firstActiveRow = activeRange.getRow();
  const columnCount = sheet.getMaxColumns();

  // Tests that the selection is a valid range.
  if (rowCount < 1) {
    showMessage("Select a valid range.");
    return;
  }
  // Tests active range isn't too large to process. Enforces limit set to 10k.
  if (rowCount > 10000) {
    showMessage(
      "Selected range too large. Select up to 10,000 rows at one time.",
    );
    return;
  }

  // Utilizes an array of values for efficient processing to determine blank rows.
  const activeRangeValues = sheet
    .getRange(firstActiveRow, 1, rowCount, columnCount)
    .getValues();

  // Checks if array is all empty values.
  const valueFilter = (value) => value !== "";
  const isRowEmpty = (row) => {
    return row.filter(valueFilter).length === 0;
  };

  // Maps the range values as an object with value (to test) and corresponding row index (with offset from selection).
  const rowsToDelete = activeRangeValues
    .map((row, index) => ({ row, offset: index + activeRange.getRowIndex() }))
    .filter((item) => isRowEmpty(item.row)) // Test to filter out non-empty rows.
    .map((item) => item.offset); //Remap to include just the row indexes that will be removed.

  // Combines a sorted, ascending list of indexes into a set of ranges capturing consecutive values as start/end ranges.
  // Combines sequential empty rows for faster processing.
  const rangesToDelete = rowsToDelete.reduce((ranges, index) => {
    const currentRange = ranges[ranges.length - 1];
    if (currentRange && index === currentRange[1] + 1) {
      currentRange[1] = index;
      return ranges;
    }
    ranges.push([index, index]);
    return ranges;
  }, []);

  // Sends a list of row indexes to be deleted to the console.
  console.log(rangesToDelete);

  // Deletes the rows using REVERSE order to ensure proper indexing is used.
  for (const [start, end] of rangesToDelete.reverse()) {
    sheet.deleteRows(start, end - start + 1);
  }
  SpreadsheetApp.flush();
}

/**
 * Removes blank columns in a selected range.
 *
 * Cells containing Space characters are treated as non-empty.
 * The entire column, including cells outside of the selected range,
 * must be empty to be deleted.
 *
 * Called from menu option.
 */
function deleteEmptyColumns() {
  const sheet = SpreadsheetApp.getActiveSheet();

  // Gets active selection and dimensions.
  const activeRange = sheet.getActiveRange();
  const rowCountMax = sheet.getMaxRows();
  const columnWidth = activeRange.getWidth();
  const firstActiveColumn = activeRange.getColumn();

  // Tests that the selection is a valid range.
  if (columnWidth < 1) {
    showMessage("Select a valid range.");
    return;
  }
  // Tests active range is not too large to process. Enforces limit set to 1k.
  if (columnWidth > 1000) {
    showMessage(
      "Selected range too large. Select up to 10,000 rows at one time.",
    );
    return;
  }

  // Utilizes an array of values for efficient processing to determine blank columns.
  const activeRangeValues = sheet
    .getRange(1, firstActiveColumn, rowCountMax, columnWidth)
    .getValues();

  // Transposes the array of range values so it can be processed in order of columns.
  const activeRangeValuesTransposed = activeRangeValues[0].map((_, colIndex) =>
    activeRangeValues.map((row) => row[colIndex]),
  );

  // Checks if array is all empty values.
  const valueFilter = (value) => value !== "";
  const isColumnEmpty = (column) => {
    return column.filter(valueFilter).length === 0;
  };

  // Maps the range values as an object with value (to test) and corresponding column index (with offset from selection).
  const columnsToDelete = activeRangeValuesTransposed
    .map((column, index) => ({ column, offset: index + firstActiveColumn }))
    .filter((item) => isColumnEmpty(item.column)) // Test to filter out non-empty rows.
    .map((item) => item.offset); //Remap to include just the column indexes that will be removed.

  // Combines a sorted, ascending list of indexes into a set of ranges capturing consecutive values as start/end ranges.
  // Combines sequential empty columns for faster processing.
  const rangesToDelete = columnsToDelete.reduce((ranges, index) => {
    const currentRange = ranges[ranges.length - 1];
    if (currentRange && index === currentRange[1] + 1) {
      currentRange[1] = index;
      return ranges;
    }
    ranges.push([index, index]);
    return ranges;
  }, []);

  // Sends a list of column indexes to be deleted to the console.
  console.log(rangesToDelete);

  // Deletes the columns using REVERSE order to ensure proper indexing is used.
  for (const [start, end] of rangesToDelete.reverse()) {
    sheet.deleteColumns(start, end - start + 1);
  }
  SpreadsheetApp.flush();
}

/**
 * Trims all of the unused rows and columns outside of selected data range.
 *
 * Called from menu option.
 */
function cropSheet() {
  const dataRange = SpreadsheetApp.getActiveSheet().getDataRange();
  const sheet = dataRange.getSheet();

  let numRows = dataRange.getNumRows();
  let numColumns = dataRange.getNumColumns();

  const maxRows = sheet.getMaxRows();
  const maxColumns = sheet.getMaxColumns();

  const numFrozenRows = sheet.getFrozenRows();
  const numFrozenColumns = sheet.getFrozenColumns();

  // If last data row is less than maximium row, then deletes rows after the last data row.
  if (numRows < maxRows) {
    numRows = Math.max(numRows, numFrozenRows + 1); // Don't crop empty frozen rows.
    sheet.deleteRows(numRows + 1, maxRows - numRows);
  }

  // If last data column is less than maximium column, then deletes columns after the last data column.
  if (numColumns < maxColumns) {
    numColumns = Math.max(numColumns, numFrozenColumns + 1); // Don't crop empty frozen columns.
    sheet.deleteColumns(numColumns + 1, maxColumns - numColumns);
  }
}

/**
 * Copies value of active cell to the blank cells beneath it.
 * Stops at last row of the sheet's data range if only blank cells are encountered.
 *
 * Called from menu option.
 */
function fillDownData() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // Gets sheet's active cell and confirms it's not empty.
  const activeCell = sheet.getActiveCell();
  const activeCellValue = activeCell.getValue();

  if (!activeCellValue) {
    showMessage("The active cell is empty. Nothing to fill.");
    return;
  }

  // Gets coordinates of active cell.
  const column = activeCell.getColumn();
  const row = activeCell.getRow();

  // Gets entire data range of the sheet.
  const dataRange = sheet.getDataRange();
  const dataRangeRows = dataRange.getNumRows();

  // Gets trimmed range starting from active cell to the end of sheet data range.
  const searchRange = dataRange.offset(
    row - 1,
    column - 1,
    dataRangeRows - row + 1,
    1,
  );
  const searchValues = searchRange.getDisplayValues();

  // Find the number of empty rows below the active cell.
  let i = 1; // Start at 1 to skip the ActiveCell.
  while (searchValues[i] && searchValues[i][0] === "") {
    i++;
  }

  // If blanks exist, fill the range with values.
  if (i > 1) {
    const fillRange = searchRange.offset(0, 0, i, 1).setValue(activeCellValue);
    //sheet.setActiveRange(fillRange) // Uncomment to test affected range.
  } else {
    showMessage("There are no empty cells below the Active Cell to fill.");
  }
}

/**
 * A helper function to display messages to user.
 *
 * @param {string} message - Message to be displayed.
 * @param {string} caller - {Optional} text to append to title.
 */
function showMessage(message, caller) {
  // Sets the title using the APP_TITLE variable; adds optional caller string.
  let title = APP_TITLE;
  if (caller != null) {
    title += ` : ${caller}`;
  }

  const ui = SpreadsheetApp.getUi();
  ui.alert(title, message, ui.ButtonSet.OK);
}

Menu.gs

solutions/editor-add-on/clean-sheet/Menu.js

在 GitHub 上查看

/**
 * Copyright 2022 Google LLC
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

/**
 * Creates a menu entry in the Google Sheets Extensions menu when the document is opened.
 *
 * @param {object} e The event parameter for a simple onOpen trigger.
 */
function onOpen(e) {
  // Builds a menu that displays under the Extensions menu in Sheets.
  const menu = SpreadsheetApp.getUi().createAddonMenu();

  menu
    .addItem("Delete blank rows (from selected rows only)", "deleteEmptyRows")
    .addItem(
      "Delete blank columns (from selected columns only)",
      "deleteEmptyColumns",
    )
    .addItem("Crop sheet to data range", "cropSheet")
    .addSeparator()
    .addItem("Fill in blank rows below", "fillDownData")
    .addSeparator()
    .addItem("About", "aboutApp")
    .addToUi();
}

/**
 * Runs when the add-on is installed; calls onOpen() to ensure menu creation and
 * any other initializion work is done immediately. This method is only used by
 * the desktop add-on and is never called by the mobile version.
 *
 * @param {object} e The event parameter for a simple onInstall trigger.
 */
function onInstall(e) {
  onOpen(e);
}

/**
 * About box for context and developer contact information.
 * TODO: Personalize
 */
function aboutApp() {
  const msg = `
  Name: ${APP_TITLE}
  Version: 1.0
  Contact: <Developer Email Goes Here>`;

  const ui = SpreadsheetApp.getUi();
  ui.alert("About this application", msg, ui.ButtonSet.OK);
}

贡献者

此示例由 Google 在 Google 开发者专家的帮助下维护。

后续步骤

• 使用插件扩展 Google 表格
• 电子表格服务参考文档