Coding level: Beginner
Duration: 15 minutes
Project type: Automation with a custom menu
Objectives
- Understand what the solution does.
- Understand what the Apps Script services do within the
solution.
- Set up the script.
- Run the script.
About this solution
To save time and reduce errors from manually copy and pasting, you can
automatically import content from multiple documents into one main document.
This solution focuses on aggregating project status reports, but you can edit it
to fit your needs.
How it works
The script creates a folder to store the documents from which you want to import
content and a template document to start importing from. The script also
includes functions that create sample documents to demo this solution.
When a user selects Import summaries from the custom menu, the script gets
all the Docs files in the folder and iterates through each. The script looks for
a specific string and heading type to identify the summary text it needs to
copy. After the text is copied, the script changes the text color of the
identifier string to mitigate duplication. The script pastes the summaries into
the main document, each in its own single-cell table.
Apps Script services
This solution uses the following services:
- Document service–Creates the template and
sample source documents. Iterates through each source document looking for
new project summaries to import. Imports the summaries to the main document.
Updates the source documents to prevent summaries from being imported more
than once.
- Drive service–Creates a folder to store the
source documents. Adds the template document and sample source documents to
the folder.
- Utilities service–Formats the date that
the script adds to the main document each time the script imports summaries
from the source documents.
- Base service–Uses the
Session class to get
the script's time zone. The script uses the time zone when adding the date of
the import to the main document.
Prerequisites
To use this sample, you need the following prerequisites:
- A Google Account (Google Workspace accounts might
require administrator approval).
- A web browser with access to the internet.
Set up the script
Click the button below to make a copy of the Aggregate content document.
Make a copy
Run the script
Run a demo with sample documents
- Click Import summaries > Configure
> Run demo setup with sample documents.
You might need to refresh the page for this custom menu to appear.
When prompted, authorize the script.
If the OAuth consent screen displays the warning, This app isn't verified,
continue by selecting Advanced >
Go to {Project Name} (unsafe).
Click Import summaries > Configure
> Run demo setup with sample documents
again.
When prompted, copy the URL of the Drive folder for use in a
later step.
Click OK.
Click Import summaries > Import
summaries.
When prompted, click OK.
Review the project summaries that were imported from the sample documents.
Add & import a summary
- In a new browser tab, paste the folder URL to open the Project status
folder.
- Open the Project ABC file.
- Create a new summary to import by adding the following content to the end of
the document:
- Type
Summary and set the text style to Heading 3.
- Directly underneath
Summary, insert a 1x1 table. Make sure there are no
blank lines in between Summary and the table.
- In the table, type
Hello world!.
- Switch back to the main document and click Import summaries
> Import summaries.
- When prompted, click OK.
- View your latest import at the end of the document.
Review the code
To review the Apps Script code for this solution, click
View source code below:
View source code
Setup.gs
/**
* 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
}
Contributors
This sample is maintained by Google with the help of Google Developer Experts.
Next steps
