Google Scripts - Adding a Template Table to the Top of a Meeting Agenda On Demand

That seems like a weird title for this post, but I was trying to be as descriptive as possible - though the script will work for other uses as well with some easy modifications. 

I was posed the following problem by a person at one of our local districts: "Looking for a way for teachers on a [district] Team to be able to click on a link so that this blank template shows up above their prior week's entries. Does this make sense?"  As I have not done much with using scripts in documents, I was eager to try this.

Surprisingly, I was able to craft this one in less than 24 hours!  And now, I see a myriad of ways that it could be used in other situations.

What this script does:  

When opening the document, an additional menu is added (1).  When the user selects to "Add a Table" from the menu, it adds an additional template table (2) below the top level document table, but above everything else.   It also adds a horizontal rule between the tables. 

Here is the script:

Add highlighted script below into your script editor within the document you want this to be in.

//Add menu to document 
function onOpen() {
  DocumentApp.getUi()
  .createMenu('New Table')
  .addItem('Add Table', 'addTable')
  .addToUi();
}

function addTable() {
  
  var body = DocumentApp.getActiveDocument().getBody();
  
 //Add a horizontal rule & line break above existing tables 
  body.insertHorizontalRule(3);
  body.insertParagraph(4, ' ');

// Create a two-dimensional array containing the cell contents.

  var cells = [
  ['Week of', ''],
  ['Agenda/ Focus for the Week:', ' '],
  ['Notes / Questions', ' '],
  ['Next Steps / Who’s Responsible?',' '],
  ['Progress Toward SMART Goal(s)',' '],
  ['Links to SMART Goals',' ']
];

// Build a table from the array
  var table1 = body.insertTable(2,cells);
  
//Set width of first column
  table1.setColumnWidth(0, 200);

//Set Border color  
  table1.setBorderColor('#C0C0C0');

//Set header row style
var tableHeader = table1.getRow(0);

  var headerStyle = {};  
headerStyle[DocumentApp.Attribute.BACKGROUND_COLOR] = '#C0C0C0';  
headerStyle[DocumentApp.Attribute.BOLD] = true; 
  
  tableHeader.setAttributes(headerStyle);

}

Using & breaking down the script:

1. Create the document

First you need to create the document you are using.  Put in any information you already want in there.  For my example, we needed the top information on the document - while this is in a table, it doesn't have to be. You just will need to know how many items (ie. paragraphs, headers, tables) are at the top in order to place your new table where you want it.

2. Paste the script into the script editor

Copy the script above (everything highlighted gray) and paste it into the script editor (found under the Tools menu - script editor).  Make sure to delete out anything already there!

3. Modify the script as desired

Here is the script and the pieces that are easily modified - notes are in dark red explaining.

A. This is to add a new menu to the document - 

//Add menu to document 
function onOpen() {  //This menu will appear when you open up the document. 
  DocumentApp.getUi()
  .createMenu('New Table') //This is the name of the menu.  Make it what you wish.
  .addItem('Add Table', 'addTable')  //This is the menu item.  The first part is what appears in the menu.  The second is the function it calls (what the rest of the script is in this case, as this is the only menu item. You can add additional items to your menu using the same format.
  .addToUi();
}

B. Name function & get data

This is naming our function, getting the initial data, and setting up adding the horizontal rule and line break.  Note the 3 and 4 in those items - this is going to set up so that my top level table stays in position 1, I'm going to assign the new table to position 2, the new horizontal line will be position 3, the line break (paragraph) will be position 4.  Those numbers can be modified to place items in the order you want in your document.

function addTable() {  //addTable is the function name - must match what is in the addItem for it to run on selection from the menu
  
  var body = DocumentApp.getActiveDocument().getBody(); //Grabs the current body of the document
  
 //Add a horizontal rule & line break above existing table(s) 
  body.insertHorizontalRule(3); //The 3 can be modified to place it in a different position in the doc
  body.insertParagraph(4, ' '); //The 4 can be modified to place it in a different position in the doc

C. Set up table information

This next section is to set up an array for the table you want to create.  We wanted the second column to be blank, so I put space holders.  This can be modified - additional/fewer rows determined by each line in brackets; additional/fewer columns determined by the number of items in each bracket separated by commas. 

// Create a two-dimensional array containing the cell contents.

  var cells = [
  ['Week of', ''],  //Text in each single quote area modified as wanted
  ['Agenda/ Focus for the Week:', ' '], //Text in each single quote area modified as wanted
  ['Notes / Questions', ' '], //Text in each single quote area modified as wanted
  ['Next Steps / Who’s Responsible?',' '], //Text in each single quote area modified as wanted
  ['Progress Toward SMART Goal(s)',' '], //Text in each single quote area modified as wanted
  ['Links to SMART Goals',' '] //Text in each single quote area modified as wanted
];

D. Build the table

// Build a table from the array
  var table1 = body.insertTable(2,cells); //The 2 can be modified to place it in a different position in the doc

E. Set the width of a column - in this case the first column

//Set width of first column
  table1.setColumnWidth(0, 200); //The 0 identifies the 1st column; change the 0 to identify which column to modify.  The 200 is the number of points to make the column - change this to adjust the column size.

F. Set the Border color of the table

//Set Border color  
  table1.setBorderColor('#C0C0C0'); //You need to use the hex color for the border color.  Look them up online.

G. Set the Header Row and give it a different style

//Set header row & header row style
var tableHeader = table1.getRow(0); //identifies the first row as the header row

  var headerStyle = {};  //lists the attributes that will be modified for header; you can add or not delete styles as wanted.
headerStyle[DocumentApp.Attribute.BACKGROUND_COLOR] = '#C0C0C0';  
headerStyle[DocumentApp.Attribute.BOLD] = true; 
  
  tableHeader.setAttributes(headerStyle); //sets the style to the header

4. Finish up

After you are done modifying the script, you will need to save it (save icon in script editor).  You may also want to run it to make sure there are no errors.  Remember that during this process you will need to give it permission to run. 

From now on, when you OPEN the document the new menu will appear and you can select to add your table at any time.

That's it!  A fairly easy to use and modify script, and we have covered 3 useful functions: how to add a custom menu, how to add to a document, and how to force that information in a specific order.