Massive Improvements…

Back in 2017, I wrote a post called: ‘Dabbling in a Little Scripting out of Necessity…’ At that time, I was helping setup an unconference session board, in a Google Sheet, which had linked Google Docs for collaborative note taking. It was basic, I needed help creating a script which would create docs for each session and link them into the Sheet. Well fast forward to today, I’m in a new district helping setup a session board but this time I needed to do some upgrading of my script. I would need help, as well, thankfully from an individual I came across on my researching… Jeff Everhart.

Now the part that I figured out; the upgrade to the script, has to do with WHERE each session would take place. We are still in the midst of a pandemic, so the sessions are going to be virtual. I wanted to put links to Google Meets into the session Docs. To do this, I figured out a way to create “unique code” Meets which auto-populate into the Docs using:

var meetlink = ‘’ + urlcellid ;
body.insertParagraph(4, “Google Meet Link”).setLinkUrl(meetlink).setFontFamily(‘Quicksand’).setFontSize(14).setAttributes(style);
I felt it was time to truly upgrade the original Script by having it pull each session title and facilitator name(s) from the Google Sheet and auto-populate into the correct cells, in tables, in each session’s collaborative notes Google Doc. I hunted and tried numerous times to figure this out. I eventually stumbled across After reaching out to him, via Twitter, he was able assist.

Now the script needed a little adjusting based upon Jeff’s feedback about offsetting the rowid & colid to find the correct cell for the data being pulled. Since my original script had the rowid and colid identify the cell which the link to the session Doc would be placed, I needed to offset negatively to go to cells above. The final script to pull the data from the Sheet for the Doc looks like this:
var title = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(location).getRange(rowid-4, colid).getDisplayValue();
var facilitator = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(location).getRange(rowid-2, colid).getDisplayValue()
var cells = [[“Session Title:”, title], [“Facilitator(s):”, facilitator], [“Twitter Attendees:”, “”]];
When this ran correctly, I almost leaped for joy, but remembered there were students in their classrooms. Here’s how everything looks (embedded below are the Google Sheet Conference Schedule [example] and a Google Doc Collaborative Notes for a session [example]):

A few days after running this Script, I heard a little constructive feedback about the results which had me do a little more tinkering. “Wouldn’t it be nice if the session times were included in the session Docs?” Here’s what I did…

// loop over sessions 1-3; this can be adjusted for number of sessions
for (var j = 1; j <= 3; j++) {
switch (j) {
case 1: session = “Session 1”; rowid = 7; time = “12:20 – 1:05”; break;
case 2: session = “Session 2”; rowid = 12; time = “1:10 – 1:55”; break;
case 3: session = “Session 3”; rowid = 17; time = “2:00 – 2:45”; break;
default: session = “Unknown”; break; }
Later on when filling the cells in the session Google Doc, I can reference “time” and depending on which case is running it will place that specific time sequence in the selected cell.

How have you continued to grow in your technology learning? Let me know in the comments below.

Leave a Reply

Your email address will not be published. Required fields are marked *