Subnetting in Google Sheets

I recently found a cool way of mocking out subnets when doing network design: Google Sheets. This lets you sketch out subnets quickly and without doing a lot of math. The math is automated, so it updates dynamically and it’s harder to manually screw things up. Change your VPC allocation? Your subnets can automatically update themselves.

Setting up Google Sheets

First, open this google sheet, which has a bunch of subnetting related scripts that aren’t part of Google Sheets by default. I didn’t write the scripts in this sheet, I just found them to be useful. Use File -> Make a Copy to save a copy in your own Google account. The functions which we’ll use only work when you copy the Sheet to your own account.

Then go ahead and create a new worksheet or just delete everything on the existing worksheet.

Set up any columns and rows you want for the networks you’re building. Pick out the total IP allocation you plan to use, and set up rows and columns for your various environments and subnets (Dev, Test, Prod, App Tier, DB Tier, etc).

Automating subnet calculations

Now we get to the fun stuff. We have a /20 for our entire allocation, which we will chop up between a bunch of VPC’s and then further chop up into subnets. And other than the initial allocation, we want every other cell to be automated.

Okay, so we have a /20 total allocation and we want to set up our VPC’s first. The first VPC (Dev) will start at the beginning of the allocation, and we’ll give it a /22.

=IPADDR(B2)&"/"&22

This takes the CIDR found in B2, and uses IPADDR to return just the IP portion (stripping the CIDR notation). Then we concatenate & a slash "/", then concatenate our new netmask for the Dev network &22.

Now for the next VPC (Test), we can just use IPNEXTNET to give us the next CIDR block of the same size as the Dev CIDR.

=IPNEXTNET(B3)

Now since the QA and the Prod networks are going to do the same thing (IPNEXTNET), we can just drag from the Dev cell into the QA and Prod cells. Hot gif action:

Keep automating

So our VPC’s are calculated, now we can do the subnets for the ELB tier, App tier, and so on. The first one (ELB east-1a) will use IPADDR similar to how we used it earlier, so that the first subnet will start at the first IP available in the VPC, but we’ll use a /26 netmask for each subnet.

=IPADDR(B3)&"/"&26

Then we use IPNEXTNET to fill out ELB east-1b, using the next available CIDR:

=IPNEXTNET(C3)

Then we can just drag from D3 to fill out all the other subnets:

Filling out the rest of our VPCs is basically no work

Once you have the first row done, you can just drag down and all the other rows populate with no work!

Differently sized subnets

Suppose we don’t want all subnets to be the same size (/26). Maybe we want the database subnets to be /27, how do we calculate that in Google Sheets? We know that the first database subnet starts where the last app subnet ends, so we calculate where that subnet ends using a few different functions.

=IPADD(IPADDR(J3), IPHOSTS(J3) + 2)&"/"&IPMASKLEN(J3) + 1

Let’s walk this out. We’re going to get the IP address (stripping the CIDR notation) of our last app subnet (IPADDR(J3)), get the number of hosts in that subnet (IPHOSTS(J3)), then add them together. We’re also going to add 2 on top of that. If we only added 1, we’d get the broadcast address of the app subnet, so we add 2 to carry into the next usable block. We’re also changing the size of the subnet from /26 to /27, and we do that by taking the subnet mask and adding 1, IPMASKLEN(J3) + 1. You could obviously add or subtract any number to the mask depending on how large or small you want to make it.

Dynamic updates are easy when everything is automated

Since we only hardcoded one cell in the whole spreadsheet, updates are super easy. Suppose we need to change our allocation from 10.0.0.0/20 to 192.168.0.0/20?

Fixing rate limiting

If your calculations get bigger than a few rows, you might run into Google’s rate limiting resulting in Service invoked too many times in a short time: exec qps. Try Utilities.sleep(1000) between calls. (line 0).

This is fixed by adding Utilities.sleep() to the script, just as the error message suggests. I’ve found that 100ms is enough to avoid rate limiting. Open up Tools -> Script Editor and add Utilities.sleep(100) to the while loop which starts on line 173. Don’t forget to File -> Save the script, then this error should go away.