If you’re running your own business, like myself, then you’ll likely be creating invoices and arranging documents for your accounts department by year, then month.
Every day I create new invoices, then open Google Drive, go into my Invoices folder, select the year then month and upload. It’s tedious.
For example, I have an /Invoices/
folder which is structured like this:
Invoices/
> 2021/
> 01-Jan/
- Ultimate-Courses-Invoice-#1234.pdf
- Ultimate-Courses-Invoice-#5678.pdf
> 02-Feb/
- Ultimate-Courses-Invoice-#4321.pdf
- Ultimate-Courses-Invoice-#8765.pdf
So, I automated the whole process - invoice creation and insertion into the correct “month” folder inside the correct “year”.
First, I’m now using a Google Form to enter the customer and order details. Secondly, I’m using Google Apps Script to listen for the submit event and handle the logic server-side.
Apps Script? What on earth is that I hear you ask? As Google put it “Many Google apps, one platform in the cloud”.
Yep, that’s right - talk to Gmail, Docs, Sheets, Drive, Forms, Calendar and much more. Not many folks have even heard of Apps Script. Now I’m just looking for more reasons to use it.
So, why Apps Script I hear you ask? It’s basically Node.js. It uses Google’s V8 runtime, with an API that can directly talk to not only your Google apps suite, but also communicate with external services. That’s mega power!
Not only this, but I am not an accountant. I can “Share” this “Invoices” folder with my accounts team and they have instant access to every year and month I’ve ever created an invoice for. It’s also free, before you ask why I’m not using a service.
Google Form Submit
We’re not going to dive into how to setup a Google Form, but once you’ve done it you can setup a “Trigger” and listen to a Submit event, much like you would with client-side JavaScript.
Here’s my submit event, then we’ll dive into how I dynamically construct all folders and insert the PDF:
const onFormSubmit = ({ response }) => {
// converts the form response into an object with camelCase properties
// as by default they are not
const payload = parseResponse(response.getItemResponses());
// wrapper function that uses Google's "UrlFetchApp.fetch" API
// to access my *external Node.js API* I have running on the ultimate courses domain
const { pdf, filename } = fetchPDF(payload);
// I get the "pdf" object back, along with the generated "filename"
// so now it's time to get jiggy and insert it somewhere
addToDriveFolder(pdf, filename);
};
Creating Folders by the Current Date
At this point in my program, I’ve generated a new PDF with my external API. Now I just need to drop it into the right place!
Let’s start with getting the current date. I mean, that makes the most sense right? What month is it? What year is it? Then we’ll either create those folders, or insert the PDF into them.
const getCurrentDateNames = () => {
// spin up the new Date object
const date = new Date();
// create the names with numeric prefixes
// so they appear in the filesystem in chronological order
const monthNames = [
'01-Jan', '02-Feb', '03-Mar', '04-Apr', '05-May', '06-Jun',
'07-Jul', '08-Aug', '09-Sep', '10-Oct', '11-Nov', '12-Dec',
];
// date.getMonth() returns a number, so let's lookup our Array to
// get the correct month we're in as actual text
// date.getMonth() for January would return 0 so we can use it to lookup the correct value
const month = monthNames[date.getMonth()];
// return year as a String as Apps Script "createFolder" function
// won't accept a number for the folder name
// P.S. date.getFullYear().toString() also works, I think this is nicer to interpolate
const year = `${date.getFullYear()}`;
return { month, year };
};
Now I need to create our addToDriveFolder
function we use inside the onFormSubmit
function:
const addToDriveFolder = (pdf, filename) => {
// destructure the month and year returned, the current dates!
const { month, year } = getCurrentDateNames();
// next we need to create or lookup the folders...
};
Apps Script uses DriveApp.getFolderById()
for exactly this (DriveApp docs) where we pass in a String
of the Google Drive hash (which you’ll see in the URL when you’ve clicked on the folder).
Free eBook
Directives, simple right? Wrong! On the outside they look simple, but even skilled Angular devs haven’t grasped every concept in this eBook.
- Observables and Async Pipe
- Identity Checking and Performance
- Web Components <ng-template> syntax
- <ng-container> and Observable Composition
- Advanced Rendering Patterns
- Setters and Getters for Styles and Class Bindings
I called this the rootFolder
and it returns a Folder
Object, which you can read more on here.
This Folder
Object then has a createFile()
method which accepts a blob
, or name
of the file and the contents
(which I use to create my PDF from the response of my API).
const addToDriveFolder = (pdf, filename) => {
const { month, year } = getCurrentDateNames();
const rootFolder = DriveApp.getFolderById('dK98Ka0Ksa9sLLsaKaTszIi'); // Folder ID
};
Next, we’ll create a getFolderById
function that accepts the parent
folder, and name
of the folder we’re looking for.
Why? We don’t know what the ID’s of the folders will be inside Google Drive, and so we can lookup the folders by name and then grab their ID’s. Here’s what that will look like:
const addToDriveFolder = (pdf, filename) => {
const { month, year } = getCurrentDateNames();
const rootFolder = DriveApp.getFolderById('dK98Ka0Ksa9sLLsaKaTszIi');
const getFolderById = (parent, name) => {
// our "getFolder" will create or return a folder
return DriveApp.getFolderById(getFolder(parent, name).getId());
};
const getFolder = (parent, name) => {
// we need to see if the "name" already exists
// in this "parent" folder and return it if so
// otherwise we'll create one and return that instead!
};
};
We could lookup the folders by their name, I just prefer working with a unique ID, and that’s easily returned from the getFolder
function (which we’ll come onto) which will either create a new folder or return an existing one.
The Folder
Object returned to rootFolder
now contains a .getFoldersByName()
method.
We can then pass in “2021” for example and check if it exists. If it does, the containing folder has a .next()
value. To safety check this, I’ve wrapped it inside a .hasNext()
call to be sure:
const addToDriveFolder = (pdf, filename) => {
//...
const getFolder = (parent, name) => {
const folder = parent.getFoldersByName(name);
if (folder.hasNext()) {
// we've located a folder exists under this name
const nextFolder = folder.next();
// the issue is, there *could be* multiple folders as it's getFoldersByName, not "getFolder" (singular)
// for some added safety I've done this and match the folder name
// against the name that we pass in, then return it
if (nextFolder.getName() === name) {
return nextFolder;
}
}
};
// call getFolder and pass in our rootFolder reference and the "year"
const yearFolder = getFolderById(rootFolder, year);
};
Great, so that works if the folder exists, but what if it doesn’t? We want to create it! Let’s amend the function to do so.
This is nice and easy as our parent
is a Folder
object which can also create new folders by simply calling createFolder()
:
const addToDriveFolder = (pdf, filename) => {
//...
const getFolder = (parent, name) => {
const folder = parent.getFoldersByName(name);
if (folder.hasNext()) {
const nextFolder = folder.next();
if (nextFolder.getName() === name) {
return nextFolder;
}
} else {
return parent.createFolder(name);
}
};
const yearFolder = getFolderById(rootFolder, year);
};
So now, we pass in the root folder and grab the year
or create it if it doesn’t exist. Nice!
What now? The month
!
I actually set up the getFolder
function to be pretty reusable and generic, so in fact to get the next folder (the month such as “01-Jan”) all we need to do is take the returned object bound to const yearFolder
and pass it into the same function:
const addToDriveFolder = (pdf, filename) => {
//...
const getFolder = (parent, name) => {
const folder = parent.getFoldersByName(name);
if (folder.hasNext()) {
const nextFolder = folder.next();
if (nextFolder.getName() === name) {
return nextFolder;
}
} else {
return parent.createFolder(name);
}
};
const yearFolder = getFolderById(rootFolder, year);
// pass in "yearFolder" as the next layer down
// this creates the month under the year or returns the existing month
const monthFolder = getFolderById(yearFolder, month);
};
Voila. You’re all set. As I mentioned we are returned a Folder
Object, which has a .createFile()
method attached, which means we can simply add the following line and we’re done:
const addToDriveFolder = (pdf, filename) => {
const { month, year } = getCurrentDateNames();
const rootFolder = DriveApp.getFolderById('dK98Ka0Ksa9sLLsaKaTszIi');
const getFolderById = (parent, name) => {
return DriveApp.getFolderById(getFolder(parent, name).getId());
};
const getFolder = (parent, name) => {
const folder = parent.getFoldersByName(name);
if (folder.hasNext()) {
const nextFolder = folder.next();
if (nextFolder.getName() === name) {
return nextFolder;
}
} else {
return parent.createFolder(name);
}
};
const yearFolder = getFolderById(rootFolder, year);
const monthFolder = getFolderById(yearFolder, month);
monthFolder.createFile(pdf).setName(filename);
};
I hope you enjoyed this fun little experiment and it potentially opened you up to using Apps Script, I really enjoyed working with it and it’s amazing to be able to write modern JavaScript.
🙌 If you want to learn even more, I’ve built a bunch of JS Language, DOM and Advanced JavaScript Courses which might just help you level up your JavaScript skills. You should also subscribe to our Newsletter!
Thanks for reading, and happy Apps Scripting!