匯總多份文件的內容

程式設計層級：新手
時間長度：15 分鐘
專案類型：使用自訂選單進行自動化作業

目標

  • 瞭解解決方案的功能。
  • 瞭解 Apps Script 服務在解決方案中的功能。
  • 設定指令碼。
  • 執行指令碼。

認識這項解決方案

如要節省時間，並減少手動複製及貼上時發生的錯誤，您可以將多份文件的內容自動匯入一份主要文件。本解決方案著重於匯總專案狀態報表，但您可以依需求編輯。

匯入專案摘要的螢幕截圖

運作方式

指令碼會建立資料夾來儲存要匯入內容的文件，以及開始匯入來源文件的範本文件。指令碼也包含函式，可用於建立範例文件來示範這項解決方案。

當使用者從自訂選單中選取「ImportSummary」時，指令碼會取得資料夾中的所有文件檔案，並反覆執行一遍。指令碼會尋找特定字串和標題類型，以識別需要複製的摘要文字。複製文字後，指令碼會變更 ID 字串的文字顏色，減少重複內容。這個指令碼會將摘要貼到主要文件中，每個文件都有專屬的單一儲存格資料表。

Apps Script 服務

這項解決方案使用下列服務：

  • 文件服務：建立範本和來源文件範例。反覆查看每份來源文件，尋找要匯入的新專案摘要。將摘要匯入主要文件。更新來源文件，防止摘要重複匯入。
  • 雲端硬碟服務：建立資料夾來儲存來源文件。將範本文件和範例來源文件新增至資料夾。
  • 公用程式服務：每次指令碼從來源文件匯入摘要時，指令碼都會加入主要文件的日期。
  • 基礎服務：使用 Session 類別取得指令碼的時區。在主要文件中新增匯入日期時，這個指令碼會使用時區。

必要條件

如要使用這個範例，您必須具備以下必要條件：

  • Google 帳戶 (Google Workspace 帳戶可能需要取得管理員核准)。
  • 可連上網際網路的網路瀏覽器。

設定指令碼

點選下方按鈕即可建立匯總內容文件的副本。
建立副本

執行指令碼

執行範例文件的示範

  1. 依序按一下「匯入摘要」>「設定」>「執行範例文件執行示範設定」。您可能需要重新整理頁面，系統才會顯示這個自訂選單。

  2. 出現提示訊息時，請授權執行指令碼。如果 OAuth 同意畫面顯示警告「This app has not verification」(這個應用程式尚未驗證)，請依序選取「Advanced」(進階) >「Go to {Project Name} (unsafe)」 (前往 {Project Name} (不安全))。

  3. 再次按一下「ImportSummary」>「Configure」> 再次點選「Run demo settings with example」 。

  4. 出現提示時，請複製雲端硬碟資料夾的網址，用於後續步驟。

  5. 點選「OK」。

  6. 依序點選「ImportSummary」>「Import 匯總」。

  7. 系統提示時，按一下「OK」。

  8. 查看從範例文件匯入的專案摘要。

新增及匯入摘要

  1. 在新的瀏覽器分頁中貼上資料夾網址，即可開啟「Project status」資料夾。
  2. 開啟 Project ABC 檔案。
  3. 在文件結尾新增下列內容，建立新的要匯入的摘要：
    1. 輸入 Summary，然後將文字樣式設為標題 3。
    2. 在 Summary 正下方插入 1x1 表格。確認 Summary 與資料表之間沒有空白行。
    3. 在表格中輸入 Hello world!。
  4. 切換回主要文件，然後依序點選「ImportSummary」>「Importsummary」。
  5. 系統提示時，按一下「OK」。
  6. 在文件結尾查看最近一次匯入的內容。

查看程式碼

如要查看這項解決方案的 Apps Script 程式碼，請點選下方的「查看原始碼」：

查看原始碼

Code.gs

solutions/automations/aggregate-document-content/Code.js
// To learn how to use this script, refer to the documentation:
// https://developers.google.com/apps-script/samples/automations/aggregate-document-content

/*
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.
*/

/** 
 * This file containts the main application functions that import data from
 * summary documents into the body of the main document.
 */

// Application constants
const APP_TITLE = 'Document summary importer'; // Application name
const PROJECT_FOLDER_NAME = 'Project statuses'; // Drive folder for the source files.

// Below are the parameters used to identify which content to import from the source documents
// and which content has already been imported.
const FIND_TEXT_KEYWORDS = 'Summary'; // String that must be found in the heading above the table (case insensitive).
const APP_STYLE = DocumentApp.ParagraphHeading.HEADING3; // Style that must be applied to heading above the table.
const TEXT_COLOR = '#2e7d32'; // Color applied to heading after import to avoid duplication.

/**
 * Updates the main document, importing content from the source files.
 * Uses the above parameters to locate content to be imported.
 * 
 * Called from menu option.
 */
function performImport() {
  // Gets the folder in Drive associated with this application.
  const folder = getFolderByName_(PROJECT_FOLDER_NAME);
  // Gets the Google Docs files found in the folder. 
  const files = getFiles(folder);

  // Warns the user if the folder is empty.
  const ui = DocumentApp.getUi();
  if (files.length === 0) {
    const msg =
      `No files found in the folder '${PROJECT_FOLDER_NAME}'.
      Run '${MENU.SETUP}' | '${MENU.SAMPLES}' from the menu
      if you'd like to create samples files.`
    ui.alert(APP_TITLE, msg, ui.ButtonSet.OK);
    return;
  }

  /** Processes main document */
  // Gets the active document and body section.
  const docTarget = DocumentApp.getActiveDocument();
  const docTargetBody = docTarget.getBody();

  // Appends import summary section to the end of the target document. 
  // Adds a horizontal line and a header with today's date and a title string.
  docTargetBody.appendHorizontalRule();
  const dateString = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'MMMM dd, yyyy');
  const headingText = `Imported: ${dateString}`;
  docTargetBody.appendParagraph(headingText).setHeading(APP_STYLE);
  // Appends a blank paragraph for spacing.
  docTargetBody.appendParagraph(" ");

  /** Process source documents */
  // Iterates through each source document in the folder.
  // Copies and pastes new updates to the main document.
  let noContentList = [];
  let numUpdates = 0;
  for (let id of files) {

    // Opens source document; get info and body.
    const docOpen = DocumentApp.openById(id);
    const docName = docOpen.getName();
    const docHtml = docOpen.getUrl();
    const docBody = docOpen.getBody();

    // Gets summary content from document and returns as object {content:content}
    const content = getContent(docBody);

    // Logs if document doesn't contain content to be imported.
    if (!content) {
      noContentList.push(docName);
      continue;
    }
    else {
      numUpdates++
      // Inserts content into the main document.
      // Appends a title/url reference link back to source document.
      docTargetBody.appendParagraph('').appendText(`${docName}`).setLinkUrl(docHtml);
      // Appends a single-cell table and pastes the content.
      docTargetBody.appendTable(content);
    }
    docOpen.saveAndClose()
  }
  /** Provides an import summary */
  docTarget.saveAndClose();
  let msg = `Number of documents updated: ${numUpdates}`
  if (noContentList.length != 0) {
    msg += `\n\nThe following documents had no updates:`
    for (let file of noContentList) {
      msg += `\n ${file}`;
    }
  }
  ui.alert(APP_TITLE, msg, ui.ButtonSet.OK);
}

/**
 * Updates the main document drawing content from source files.
 * Uses the parameters at the top of this file to locate content to import.
 * 
 * Called from performImport().
 */
function getContent(body) {

  // Finds the heading paragraph with matching style, keywords and !color.
  var parValidHeading;
  const searchType = DocumentApp.ElementType.PARAGRAPH;
  const searchHeading = APP_STYLE;
  let searchResult = null;

  // Gets and loops through all paragraphs that match the style of APP_STYLE.
  while (searchResult = body.findElement(searchType, searchResult)) {
    let par = searchResult.getElement().asParagraph();
    if (par.getHeading() == searchHeading) {
      // If heading style matches, searches for text string (case insensitive).
      let findPos = par.findText('(?i)' + FIND_TEXT_KEYWORDS);
      if (findPos !== null) {

        // If text color is green, then the paragraph isn't a new summary to copy.
        if (par.editAsText().getForegroundColor() != TEXT_COLOR) {
          parValidHeading = par;
        }
      }
    }
  }

  if (!parValidHeading) {
    return;
  } else {
    // Updates the heading color to indicate that the summary has been imported.     
    let style = {};
    style[DocumentApp.Attribute.FOREGROUND_COLOR] = TEXT_COLOR;
    parValidHeading.setAttributes(style);
    parValidHeading.appendText(" [Exported]");

    // Gets the content from the table following the valid heading.
    let elemObj = parValidHeading.getNextSibling().asTable();
    let content = elemObj.copy();

    return content;
  }
}

/**
 * Gets the IDs of the Docs files within the folder that contains source files.
 * 
 * Called from function performImport().
 */
function getFiles(folder) {
  // Only gets Docs files.
  const files = folder.getFilesByType(MimeType.GOOGLE_DOCS);
  let docIDs = [];
  while (files.hasNext()) {
    let file = files.next();
    docIDs.push(file.getId());
  }
  return docIDs;
}

solutions/automations/aggregate-document-content/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.
 */

/** 
 * This file contains the functions that build the custom menu.
 */
// Menu constants for easy access to update.
const MENU = {
  NAME: 'Import summaries',
  IMPORT: 'Import summaries',
  SETUP: 'Configure',
  NEW_INSTANCE: 'Setup new instance',
  TEMPLATE: 'Create starter template',
  SAMPLES: 'Run demo setup with sample documents'
}

/**
 * Creates custom menu when the document is opened.
 */
function onOpen() {
  const ui = DocumentApp.getUi();
  ui.createMenu(MENU.NAME)
    .addItem(MENU.IMPORT, 'performImport')
    .addSeparator()
    .addSubMenu(ui.createMenu(MENU.SETUP)
      .addItem(MENU.NEW_INSTANCE, 'setupConfig')
      .addItem(MENU.TEMPLATE, 'createSampleFile')
      .addSeparator()
      .addItem(MENU.SAMPLES, 'setupWithSamples'))
    .addItem('About', 'aboutApp')
    .addToUi()
}

/**
 * About box for context and contact.
 * TODO: Personalize
 */
function aboutApp() {
  const msg = `
  ${APP_TITLE}
  Version: 1.0
  Contact: <Developer Email goes here>`

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

Setup.gs

solutions/automations/aggregate-document-content/Setup.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.
 */

/** 
 * This file contains functions that create the template and sample documents.
 */

/**
 * Runs full setup configuration, with option to include samples.
 * 
 * Called from menu & setupWithSamples()
 * 
 * @param {boolean} includeSamples - Optional, if true creates samples files. * 
 */
function setupConfig(includeSamples) {

  // Gets folder to store documents in.
  const folder = getFolderByName_(PROJECT_FOLDER_NAME)

  let msg =
    `\nDrive Folder for Documents: '${PROJECT_FOLDER_NAME}'
   \nURL: \n${folder.getUrl()}`

  // Creates sample documents for testing.
  // Remove sample document creation and add your own process as needed.
  if (includeSamples) {
    let filesCreated = 0;
    for (let doc of samples.documents) {
      filesCreated += createGoogleDoc(doc, folder, true);
    }
    msg += `\n\nFiles Created: ${filesCreated}`
  }
  const ui = DocumentApp.getUi();
  ui.alert(`${APP_TITLE} [Setup]`, msg, ui.ButtonSet.OK);

}

/**
 * Creates a single document instance in the application folder.
 * Includes import settings already created [Heading | Keywords | Table]
 * 
 * Called from menu. 
 */
function createSampleFile() {

  // Creates a new Google Docs document.
  const templateName = `[Template] ${APP_TITLE}`;
  const doc = DocumentApp.create(templateName);
  const docId = doc.getId();

  const msg = `\nDocument created: '${templateName}'
  \nURL: \n${doc.getUrl()}`

  // Adds template content to the body.
  const body = doc.getBody();

  body.setText(templateName);
  body.getParagraphs()[0].setHeading(DocumentApp.ParagraphHeading.TITLE);
  body.appendParagraph('Description').setHeading(DocumentApp.ParagraphHeading.HEADING1);
  body.appendParagraph('');

  const dateString = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'MMMM dd, yyyy');
  body.appendParagraph(`${FIND_TEXT_KEYWORDS} - ${dateString}`).setHeading(APP_STYLE);
  body.appendTable().appendTableRow().appendTableCell('TL;DR');
  body.appendParagraph("");

  // Gets folder to store documents in.
  const folder = getFolderByName_(PROJECT_FOLDER_NAME)

  // Moves document to application folder.
  DriveApp.getFileById(docId).moveTo(folder);

  const ui = DocumentApp.getUi();
  ui.alert(`${APP_TITLE} [Template]`, msg, ui.ButtonSet.OK);
}

/**
 * Configures application for demonstration by setting it up with sample documents.
 * 
 * Called from menu | Calls setupConfig with option set to true. 
 */
function setupWithSamples() {
  setupConfig(true)
}

/** 
 * Sample document names and demo content. 
 * {object} samples[]
*/
const samples = {
  'documents': [
    {
      'name': 'Project GHI',
      'description': 'Google Workspace Add-on inventory review.',
      'content': 'Reviewed all of the currently in-use and proposed Google Workspace Add-ons. Will perform an assessment on how we can reduce overlap, reduce licensing costs, and limit security exposures. \n\nNext week\'s goal is to report findings back to the Corp Ops team.'
    },
    {
      'name': 'Project DEF',
      'description': 'Improve IT networks within the main corporate building.',
      'content': 'Primarily focused on 2nd thru 5th floors in the main corporate building evaluating the network infrastructure. Benchmarking tests were performed and results are being analyzed. \n\nWill submit all findings, analysis, and recommendations next week for committee review.'
    },
    {
      'name': 'Project ABC',
      'description': 'Assess existing Google Chromebook inventory and recommend upgrades where necessary.',
      'content': 'Concluded a pilot program with the Customer Service department to perform inventory and update inventory records with Chromebook hardware, Chrome OS versions, and installed apps. \n\nScheduling a work plan and seeking necessary go-forward approvals for next week.'
    },
  ],
  'common': 'This sample document is configured to work with the Import summaries custom menu. For the import to work, the source documents used must contain a specific keyword (currently set to "Summary"). The keyword must reside in a paragraph with a set style (currently set to "Heading 3") that is directly followed by a single-cell table. The table contains the contents to be imported into the primary document.\n\nWhile those rules might seem precise, it\'s how the application programmatically determines what content is meant to be imported and what can be ignored. Once a summary has been imported, the script updates the heading font to a new color (currently set to Green, hex \'#2e7d32\') to ensure the app ignores it in future imports. You can change these settings in the Apps Script code.'
}

/**
 * Creates a sample document in application folder.
 * Includes import settings already created [Heading | Keywords | Table].
 * Inserts demo data from samples[].
 * 
 * Called from menu. 
 */
function createGoogleDoc(document, folder, duplicate) {

  // Checks for duplicates.
  if (!duplicate) {
    // Doesn't create file of same name if one already exists.
    if (folder.getFilesByName(document.name).hasNext()) {
      return 0 // File not created.
    }
  }

  // Creates a new Google Docs document.
  const doc = DocumentApp.create(document.name).setName(document.name);
  const docId = doc.getId();

  // Adds boilerplate content to the body.
  const body = doc.getBody();

  body.setText(document.name);
  body.getParagraphs()[0].setHeading(DocumentApp.ParagraphHeading.TITLE);
  body.appendParagraph("Description").setHeading(DocumentApp.ParagraphHeading.HEADING1);
  body.appendParagraph(document.description);
  body.appendParagraph("Usage Instructions").setHeading(DocumentApp.ParagraphHeading.HEADING1);
  body.appendParagraph(samples.common);

  const dateString = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'MMMM dd, yyyy');
  body.appendParagraph(`${FIND_TEXT_KEYWORDS} - ${dateString}`).setHeading(APP_STYLE);
  body.appendTable().appendTableRow().appendTableCell(document.content);
  body.appendParagraph("");

  // Moves document to application folder.
  DriveApp.getFileById(docId).moveTo(folder);

  // Returns if successfully created.
  return 1
}

Utilities.gs

solutions/automations/aggregate-document-content/Utilities.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.
 */

/** 
 * This file contains common utility functions.
 */

/**
 * Returns a Drive folder located in same folder that the application document is located.
 * Checks if the folder exists and returns that folder, or creates new one if not found.
 *
 * @param {string} folderName - Name of the Drive folder. 
 * @return {object} Google Drive folder
 */
function getFolderByName_(folderName) {
  // Gets the Drive folder where the current document is located.
  const docId = DocumentApp.getActiveDocument().getId();
  const parentFolder = DriveApp.getFileById(docId).getParents().next();

  // Iterates subfolders to check if folder already exists.
  const subFolders = parentFolder.getFolders();
  while (subFolders.hasNext()) {
    let folder = subFolders.next();

    // Returns the existing folder if found.
    if (folder.getName() === folderName) {
      return folder;
    }
  }
  // Creates a new folder if one doesn't already exist.
  return parentFolder.createFolder(folderName)
    .setDescription(`Created by ${APP_TITLE} application to store documents to process`);
}

/**
 * Test function to run getFolderByName_.
 * @logs details of created Google Drive folder.
 */
function test_getFolderByName() {

  // Gets the folder in Drive associated with this application.
  const folder = getFolderByName_(PROJECT_FOLDER_NAME);

  console.log(`Name: ${folder.getName()}\rID: ${folder.getId()}\rURL:${folder.getUrl()}\rDescription: ${folder.getDescription()}`)
  // Uncomment the following to automatically delete the test folder.
  // folder.setTrashed(true);
}

貢獻者

本範例是由 Google 在 Google Developers 專家的協助下維護。

後續步驟