스프레드시트의 데이터 정리

코딩 수준: 중급
소요 시간: 20분
프로젝트 유형: 편집기 부가기능

목표

  • 솔루션의 작동 방식을 이해합니다.
  • 솔루션 내에서 Apps Script 서비스가 하는 작업을 이해합니다.
  • 스크립트를 설정합니다.
  • 스크립트를 실행합니다.

이 솔루션 정보

빈 행과 열을 자동으로 삭제하고, 스프레드시트를 데이터 범위의 가장자리로 자르고, 데이터의 공백을 채워 스프레드시트 데이터를 정리합니다.

스프레드시트 서식을 지정하는 부가기능 데모

작동 방식

스크립트는 다음 함수를 실행합니다.

  • 빈 행 삭제: 선택한 범위 내에서 스크립트가 빈 행을 식별하고 삭제합니다. 행 내 셀에 공백 문자가 포함되어 있으면 행이 비어 있는 것으로 간주되지 않습니다.
  • 빈 열 삭제: 선택한 범위 내에서 스크립트가 빈 열을 식별하고 삭제합니다. 열 내 셀에 공백 문자가 포함되어 있으면 열이 비어 있는 것으로 간주되지 않습니다.
  • 데이터 범위로 시트 자르기: 스크립트가 데이터 범위가 끝나는 위치를 식별하고 초과 행과 열을 삭제합니다.
  • 빈 행 채우기: 선택한 활성 셀의 콘텐츠를 복사하여 그 아래 행의 빈 셀에 붙여넣는 스크립트입니다. 스크립트는 비어 있지 않은 행을 만나거나 데이터 범위의 끝에 도달하면 콘텐츠 붙여넣기를 중지합니다.

Apps Script 서비스

이 솔루션은 다음 서비스를 사용합니다.

기본 요건

이 샘플을 사용하려면 다음 기본 요건이 필요합니다.

  • Google 계정 (Google Workspace 계정의 경우 관리자 승인이 필요할 수 있음)
  • 인터넷에 액세스할 수 있는 웹브라우저

스크립트 설정

  1. 다음 버튼을 클릭하여 백지 Apps Script 프로젝트를 엽니다.
    프로젝트 열기
  2. 개요 를 클릭합니다.
  3. 개요 페이지에서 '사본 만들기'를 클릭합니다. 사본을 만들기 위한 아이콘
  4. 복사한 프로젝트 상단에서 배포 > 테스트 배포를 클릭합니다.
  5. 유형 선택 옆에 있는 배포 유형 사용 설정 프로젝트 설정의 아이콘 > 편집기 부가기능을 클릭합니다.
  6. 새 테스트 만들기를 클릭합니다.
  7. 테스트 문서에서 선택된 문서 없음을 클릭합니다.
  8. 정리할 데이터가 있는 스프레드시트를 선택하고 삽입을 클릭합니다. 샘플 문서를 사용하려면 샘플 정리 데이터 스프레드시트의 사본을 만듭니다.
  9. 테스트 저장을 클릭합니다.
  10. 스프레드시트를 열려면 저장된 테스트 옆에 있는 라디오 버튼을 선택하고 실행을 클릭합니다.

스크립트 실행

  1. 스프레드시트에서 범위 A1:F20를 선택합니다.
  2. 확장 프로그램 > 클린 시트 사본 > 빈 행 삭제를 클릭합니다.
  3. 메시지가 표시되면 계속을 클릭하고 스크립트를 승인합니다.
  4. 확장 프로그램 > 클린 시트 사본 > 빈 행 삭제를 다시 클릭합니다.
  5. 확장 프로그램 > 클린 시트 사본 > 빈 열 삭제를 클릭합니다.
  6. 확장 프로그램 > 정리된 시트 사본 > 데이터 범위로 시트 자르기를 클릭합니다.
  7. 셀 C7를 선택합니다.
  8. 확장 프로그램 > 클린 시트 사본 > 아래 빈 행 채우기를 클릭합니다.

코드 검토

이 솔루션의 Apps Script 코드를 검토하려면 아래의 소스 코드 보기를 클릭합니다.

소스 코드 보기

Code.gs

solutions/editor-add-on/clean-sheet/Code.js
// 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.
  let 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.
  rangesToDelete.reverse().forEach(([start, end]) => 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.
  let 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.
  rangesToDelete.reverse().forEach(([start, end]) => 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.
  const title = APP_TITLE
  if (caller != null) {
    title += ` : ${caller}`
  };

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

solutions/editor-add-on/clean-sheet/Menu.js
/**
 * 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.
  let 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 개발자 전문가의 도움을 받아 유지관리합니다.

다음 단계