In the first part of this article, I explored my decision to free up from Evernote to a custom solution using Google Drive. I set up a simple workflow with Google Sheets to organize tasks, categorize projects, and link other Google Docs and Sheets. Using conditional formatting and custom columns to visually manage priorities, due dates, and tags, worked well but still required manual effort. Here Google Apps Script came handy to automate tasks like creating files, adding links, and setting calendar events, creating a powerful solution only with Google Drive.
In this update, I’ll include the overall explanation of the App Script code used to automate tasks for the workflow I used to finally replace Evernote with Google Drive.
Parameters
To configure the script without altering code directly, there is a 'Parameters' sheet where some configuration will be performed.
- Column “A” has the name of the parameter to be referenced in the parameters array (see Utilities section).
- Column “B” has the value associated with the parameter.
Here is the complete list of the parameters:
Parameter | Description |
Word | Document ID for the Word Template. Defined by User. |
Spreadsheet | Document ID for the Spreadsheet Template. Defined by User. |
Folder | ID of the Destination Folder Defined by User. |
LinkColumn | Column where the link to a file will be added. Formulated field. |
CreatedColumn | Column where the Created date will be stored. Formulated field. |
AgeColumn | Column where the age will be shown in a format defined by “ItemAge”. Formulated field. |
CreatedColumnLetter | Letter of the column of the created date. Formulated field. |
ItemAge | Template to use for the AgeColumn. Defined by User. |
RequiredColumn | Column where the Required date will be stored. Used to create the calendar event. Formulated field. |
TitleColumn | Column where the Title of the item should be. Used to create the calendar event. Formulated field. |
TimeColumn | Column where the Time will be stored. Used to create the calendar event. Formulated field. |
Events
The onOpen event is used to add a new menu item and the sub-items for the defined Actions: Add New Item, Add Document, Add Spreadsheet, Add Link and Create Event
/**
* Startup configuration, add a menu with the specific options for the file.
*/
function onOpen()
{
const ui = SpreadsheetApp.getUi();
const menu = ui.createMenu('Actions');
menu.addItem('Add New Item', 'addItemRow');
menu.addItem('Add Document', 'createDocument');
menu.addItem('Add Spreadsheet', 'createSpreadsheet');
menu.addItem('Add Link', 'addEmptyLink');
menu.addSeparator();
menu.addItem('Create Event', 'createCalendarEvent');
menu.addToUi();
}
Utilities
I like to separate the general utilities from the main functionality, in that way future developments, or versions, will easily identify reusable code to be extracted to create libraries.
The utilities I used are:
- addLink - straightforward function to prepare a HYPERLINK command to a cell. It will use the function updateCellIfEmpty to finally update the required cell.
/**
* Add a Hyperlink to the active row
*
* @pColumn: column where the link will be added.
* @pURL: URL to reference in the Link.
* @pLabel: Label to show by the Link.
*/
function addLink(pColumn, pURL, pLabel)
{
const currentSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Items');
const activeRow = currentSheet.getActiveCell().getRowIndex();
updateCellIfEmpty(currentSheet, activeRow, pColumn, "=HYPERLINK(\"" + pURL + "\",\"" + pLabel + "\")");
}
- getParameters - this function will read the contents of the sheet “Parameters” and store all values in an array which is returned to the caller function.
/**
* Read parameters from "Parameters" sheet and store them in an array for later use.
*/
function getParameters()
{
const parametersSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Parameters');
const parameterRows = parametersSheet.getDataRange().getValues();
const parameters = {};
parameterRows.forEach(function(row)
{
parameters[row[0]] = row[1];
}
);
return parameters;
}
- updateCellIfEmpty - utility function to set the value of the required cell, only if it is empty
/**
* Update a Cell with the given contents only if it is empty.
*
* @pSheet: working sheet.
* @pRow: row to work with.
* @pColumn: column to work with.
* @pValue: value to store.
*/
function updateCellIfEmpty(pSheet, pRow, pColumn, pValue)
{
var cellRange = pSheet.getRange(pRow, pColumn);
var cellValue = cellRange.getValue();
if (!cellValue)
{
cellRange.setValue(pValue);
}
}
Wrappers
Simple functions to call other functionality. I used them as the function called from the menu items and have more control in case more functionality is required for a particular call.
createDocument and createSpreadsheet internally call the same function with a different parameter to instruct the function the type of document to be created.
/**
* Wrapper to create a new Word file
*/
function createDocument()
{
createNewFile('Word');
}
/**
* Wrapper to create a new Spreadsheet file
*/
function createSpreadsheet()
{
createNewFile('Excel');
}
Main functionality
Here you will find the main logic for each of the menu items.
- addEmptyLink - prepares a template to be added to a cell, internally it uses the addLink utility.
/**
* Prepare a generic template to add to the "Link" column
*/
function addEmptyLink()
{
const parameters = getParameters();
const nColumn = parameters['LinkColumn'] * 1;
addLink(nColumn, "Add Link Here", "Open Link");
}
- addItemRow - creates a new row to the “Items” sheet and prefills the aging fields.
/**
* Adds a new row to the "Items" sheet, prefilling the Created and Age columns.
*/
function addItemRow()
{
const parameters = getParameters();
const itemAgeFormula = parameters['ItemAge'];
const nCreatedColumn = parameters['CreatedColumn'] * 1;
const nAgeColumn = parameters['AgeColumn'] * 1;
const currentSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Items');
const activeRow = currentSheet.getActiveCell().getRowIndex();
if (activeRow > 1)
{
currentSheet.insertRowsBefore(activeRow, 1);
currentSheet.setActiveRange(currentSheet.getRange(activeRow,2));
updateCellIfEmpty(currentSheet, activeRow, nCreatedColumn, new Date());
updateCellIfEmpty(currentSheet, activeRow, nAgeColumn, itemAgeFormula.replace(/{{Row}}/g, activeRow));
}
}
- createCalendarEvent - Using the information of the required date and the specified time, it will access the user's calendar to create a new event.
/**
* Creates an event in the calendar, using the information form the active row
*/
function createCalendarEvent()
{
const parameters = getParameters();
const nRequiredColumn = parameters['RequiredColumn'] * 1;
const nTitleColumn = parameters['TitleColumn'] * 1;
const nTimeColumn = parameters['TimeColumn'] * 1;
const currentSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Items')
const activeRow = currentSheet.getActiveCell().getRowIndex()
if (activeRow > 1)
{
var nError = 0
// Get the Title from the predefined Cell
var cellRange = currentSheet.getRange(activeRow, nTitleColumn)
var cellRangeValues = cellRange.getValues()
var sTitle = cellRangeValues[0][0]
var dtTime = null
var dtStartDate = null
var dtEndDate = null
// Get the Time from the predefined Cell and convert it to a Date object
cellRange = currentSheet.getRange(activeRow, nTimeColumn)
cellRangeValues = cellRange.getValues()
var sTime = cellRangeValues[0][0]
try
{
dtTime = new Date(cellRangeValues[0][0])
}
catch (e)
{
dtTime = new Date()
}
// Get the date from the predefined Cell and convert it to a Date object
cellRange = currentSheet.getRange(activeRow, nRequiredColumn)
cellRangeValues = cellRange.getValues()
try
{
dtStartDate = new Date(cellRangeValues[0][0])
dtEndDate = new Date(cellRangeValues[0][0])
}
catch (e)
{
nError = 1
}
if (!nError)
{
var dtNow = new Date()
// Final validation, event is in the future
if (dtStartDate.valueOf() > dtNow.valueOf())
{
dtStartDate.setHours(dtTime.getHours())
dtEndDate.setHours(dtStartDate.getHours() + 1)
CalendarApp.getDefaultCalendar().createEvent(sTitle, dtStartDate, dtEndDate)
}
}
}
}
- createNewFile - Given the parameter, it will create a copy of a Google Docs or Google Sheets document and will store it in a cell of the row as an HYPERLINK for easier access. The template and the path to store it is defined in the parameters.
/**
* Creates a new document and add it as a link to the active row
* Warning: in case the column for the Link already has information, no update will be performed
* and the created file won't be linked to the active row
*
* @pType: Valid values are ['Word' | 'Excel']
*/
function createNewFile(pType)
{
const parameters = getParameters();
const sWordTemplate = parameters['Word'];
const sSpreadsheetTemplate = parameters['Spreadsheet'];
const sTargetFolder = parameters['Folder'];
const nColumn = parameters['LinkColumn'] * 1;
const sPrefix = 'Notes '
var sTemplate = ''
if (pType == 'Word')
{
sTemplate = sWordTemplate;
}
else
{
sTemplate = sSpreadsheetTemplate;
}
// Validate we have all the required data, then creates file
if (sTemplate.length > 0 && sTargetFolder.length > 0)
{
const fileTemplate = DriveApp.getFileById(sTemplate);
const targetFolder = DriveApp.getFolderById(sTargetFolder);
// Define file name
const sNewFileName = sPrefix + Utilities.formatDate(new Date(), "GMT-6", 'yyyy-MM-dd HH:mm');
// Create new file
const newFile = fileTemplate.makeCopy(sNewFileName, targetFolder);
addLink(nColumn, newFile.getUrl(), "Open File");
}
}
Conclusion
Despite the fact this version works as intended, I’m sure there is room for improvement.
In future versions, I’m considering additional document types and more advanced calendar features. I’d love to hear about your customizations or ideas.
Comments