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 = ‘https://meet.google.com/lookup/’ + urlcellid ;body.insertParagraph(4, “Google Meet Link”).setLinkUrl(meetlink).setFontFamily(‘Quicksand’).setFontSize(14).setAttributes(style);
@J_Everhart383 I am a novice at Apps Script and was hoping you would be able to take a look at one I’m working on and assist with a challenge…
— Dan Gallagher (@Gallagher_Tech) October 1, 2021
Sure, I thought this might be a little easier https://t.co/NUoxgISipc
— Dan Gallagher (@Gallagher_Tech) October 1, 2021
I see them thank you, follow-up question. You mentioned that this could work if I make the necessary offsets to the rowid and colid. How would I make the offset? I placed the amendments into the script and ran, it still had blank cells.
— Dan Gallagher (@Gallagher_Tech) October 1, 2021
Where n is the number or rows we need to offset by to get the correct cell reference in the spreadsheet. If you are for a cleaner way to do some of this, you could get all of the data from the sheet and then loop through that area, which should already have the data you need
— Jeff Everhart (@J_Everhart383) October 1, 2021
Thank you, thank you, thank you! With the way the rowid & colid were finding the specific cell to place the GDoc link, I had to offset by -4 for the title and -2 for the facilitator! I appreciate all your help with this!
— Dan Gallagher (@Gallagher_Tech) October 5, 2021
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:”, “”]];
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 sessionsfor (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; }