DEV Community: Al Chen

10 features where Coda is 10X better than Notion in 2022

Al Chen — Fri, 04 Mar 2022 19:21:37 +0000

See the full post with interactive elements here.

Let me be blunt: I work at Coda, and I’m 100% biased. I started using Coda during its stealth phase more than 5 years ago and frequently post videos about Coda on YouTube.

If you are looking for more “un-biased” reviews of Coda or Notion, I’d recommend checking out G2, Capterra, or TrustRadius (although those sites can contain bias as well since vendors typically send gift cards for reviews). There’s also this less-biased article from my colleague. The most unbiased review would just be to ask your colleague what they think about Coda and Notion, browse places like Reddit, or check out this guide from someone who started in Notion and ended in Coda.

Many people ask me and my colleagues what differentiates Coda from Notion and vice versa. As is the case with many SaaS software you use today, every tool has its pros and cons. To understand where I’m coming from, you need to know a little more about me. I have a background in financial analysis and love spreadsheets. So much so that I extend spreadsheets beyond what they were meant to do (balancing ledgers and whatnot for accounting folks). Beyond the simple comparison of features I share below, one key takeaway from this post is this:

You can bend a tool to do what you want, as long as you’re willing to put up with the nuance, tedium, and complexity of bending the tool.

With that preface, let’s get into the top 10 features where Coda is 10X better than Notion. If you prefer to watch a video of the top 10 features, click below:

1. Easier-to-write formulas.

Lest you forget: you can bend a tool to any shape you want. For the Excel fans out there, you may think this formula is easy to write:

=VLOOKUP(A2,Raw_Data!A1:C100,2,0)

I’m not going to lie, I do love me a good VLOOKUP when I need to do some quick analysis. Here’s a quick comparison of doing a VLOOKUP between Coda and Notion:

Coda formula for lookups

Notion “formula” for lookups

In the “Customers” table, we’re trying to find the total sales for each customer (this comes from the “Sales” table). In Coda, the formula looks like this: Sales.filter(Customer ID=Customer ID).Total Sales. In Notion, you first do a Sales Relation column and then do a rollup column called Customer Sales to get the actual number of sales. This isn’t really a formula in Notion. It’s like the “no code” way of writing a formula.

A more clear example of how writing formulas in Coda is easier might be in this simple example below:

Coda formula for IF statement

Notion formula for IF statement

We’re trying to see if the customer name is equal to “Alice.” In both Coda and Notion, you reference the entire column name instead of cell references (like Excel and Google Sheets). The main drawback with Notion is that you have to add a prop() function around each column name, which makes long formulas more difficult to parse. This could be an easy fix on Notion’s side to make their formulas easier to read.

2. Formulas that just do more in tables.

Stretching formulas to do creative things is what I love about Excel and Google Sheets. That creativity is taken to the max in Coda. In the example above, let’s say we want a column to say “High” if that sales number is is highest of all sales, “Low” if the number is the lowest, and “N/A” for everything else:

Coda formulas look pretty

Notion formulas wrap on 1 line

The Coda formula uses the Switch function which prevents the need to do a nested IF function. The Max and Min functions aren’t super special, but you can sort values in your current table and compare those values with a specific row. In the Notion example, the formula is doable, but requires a few “supporting columns” like the All Sales relation, Max Sales rollup, and Min Sales rollup. Side note: I wasn’t sure if this formula was even possible in Notion until I came across this Reddit thread. Then from there, you can write the nested IF statement to see if that customer’s sales is the highest or lowest.

I put these formula features as #1 and #2 because they show where Coda is better than Notion the most. Notion could come up with more flexible formulas that allow dynamic sorts and filters, but in the meantime the rollup and relation features will have to suffice. Again, you can always bend your tool to do what you want.

3. Automations to handle recurring tasks & reminders.

For most people out there, Coda’s automation feature is like the robot that does things for you every day or every week. It sends an update email, shows you your recurring tasks for the week, and can even send Slack messages. For the developers out there, it’s like a crontab. One of my favorite examples of automations being used in the wild is this Robot PM template by Mick Niepoth who set up a system to send reminders to his team on overdue tasks.

Coda’s Automation panel

Watch for 20 seconds starting at 1:00 to see how automations work:

General automations aren’t available in Notion, but there are some creative workarounds for setting up recurring tasks and reminders. This one from Thomas Frank and this video from Marie Poulin are worth checking out showing how they tried to build automations into their Notion workspaces:

4. Full Integrations with 3rd-party services.

In Coda, 3rd-party integrations are called Packs. In Feb 2022, we announced the open Packs platform where anyone can create their own Pack and extend the power of Coda. The main difference between Coda’s Packs and Notion’s integrations is the ease of use. This is akin to Coda applying a “no code” layer on top of integrations. What does that mean?

If you scroll up to bullet #1 where I mention the VLOOKUP example, Coda lets you write a formula to do something similar to VLOOKUP. Notion, on the other hand, abstracts the formula writing away and lets you click through menus to create relations and rollups to do something similar to VLOOKUP. This is like Notion applying “no code” to doing a VLOOKUP, where “no code” is a general term for making something a little more abstract.

Both Coda and Notion have APIs, and it’s up to makers and builders to create integrations that the masses can use. In the case of integrations (that interact with the API), Coda has applied a “no code” approach to integrating with 3rd party apps. This means simple drag-and-drop to get your data into Coda and less fiddling with APIs. Watch for 30 seconds starting at 0:50 to see how you might integrate with Gmail:

Coda’s Packs are standardized and the ease of using the GitHub Pack would be the same as using the Gmail, Twitter, and yes, even the Notion Pack. This table below shows the Yelp Pack in action. It allows you to see info about a restaurant with only the Yelp URL (see the Coda doc to interact with the table):

Even I was able to publish a Pack. My colleague and I published this Pack which simply shows you a live feed of all remote jobs from We Work Remotely.

Notion’s integrations include Asana, GitHub, and a few others created by the community. The main difference is that the extent of the integration is a “live preview” of a card in Trello or issue in GitHub when you copy and paste a link into Notion (see “Link Previews” in this help article). This screen in Notion shows all the “connected apps” available and it seems like you can see a table of your Trello cards or GitHub issues in a database in Notion, but this feature simply let’s you embed rich previews of your 3rd party data:

Notion’s integrations only allow for rich previews & embeds

Bottom line: Integrations (or Packs) in Coda are “full” integrations where you can view and sort your data from 3rd party apps, and take action in those apps by pushing a button in Coda (see the next bullet on buttons). Notion’s integrations are simply rich previews of your data (although this will change as Notion announced synced databases in March 2022).

Community-driven directory sites do exist like NotionIntegrations. You could integrate Notion with GitHub through this integration created by BeyondCode (letting you see all your GitHub data in a database), but this isn’t an “official” Notion integration. If there are any issues with the integration, you have to reach out to the BeyondCode team.

5. Buttons that look like buttons and do stuff.

Combined with formulas in bullets #1 and #2, buttons are one of the most powerful features in Coda. The main problem they solve for teams is accidental or incorrect data entry in a table. Coming from the Excel or Google Sheets worlds, you typically create buttons by using Form Controls which requires you to know how to write macros. In Coda, once you’ve become familiar with a few formulas, you get the added benefit using that same formula language in a button.

For instance, look at this example table below of tier 1 customers you might have set up for your sales team to call:

It’s a pretty basic table and if an Account Exec calls the customer, they would check off the checkbox in the Called? column. There’s some basic conditional formatting so that it’s clear which customer has been called.

If you’ve set up a Google Sheet or Notion workspace for your team, the checkbox is probably the most advanced you’ve gotten with making your spreadsheet or workspace “user friendly” to your teammates. Let’s look at the same table now with a button instead of a checkmark:

That green button just makes the list of customers a little easier to interact with versus checking off the checkbox column. We could even hide the Call check column from view to de-clutter the table some more. Behind the scenes, formulas in the Called? button column control the text that shows up on the button and the checking of the checkbox in the Call check column. Maybe you want to have a giant button outside the table that checks off all customers in one go:

If you need a more lightweight button without all the formulas (but still want a nice looking button), you can add a simple reaction button into your tables or doc. These reaction buttons are nice visual indicators letting your teammates know that you’ve approved, acknowledged, or read something.

The template button is the only native button in Notion that lets you make copies of a template you’ve built. There are work arounds for creating button-like images in Notion like this example where you create actual images in a gallery database view. Apparently there’s also a button widget you can use via Indify (3rd party), but it’s not a native Notion feature.

6. A formula editor with new line breaks, spaces, and tabs.

If you’re a heavy spreadsheet users, you’ve probably had to write a long nested IF formula with multiple true and false branches. IF functions exist in Coda and Notion too! However, in Coda there is a big formula editor that lets you see your formulas more clearly. This is a feature that is similar to bullet #1 where formulas in Coda are just easier to write.

If we go back to the example from bullet #1, here’s a comparison of what a long-ish formula looks like in Coda and Notion:

Coda’s formula editor

Notion’s formula editor

These are the formulas for Coda and Notion for figuring out the “high” and “low” sales from bullet #1. The Coda formula could’ve been written in one long line like the Notion formula, but aren’t things just nicer when you have line breaks and indents? It makes debugging formulas your colleagues write way easier when everything is not written on one long wrapped line.

If you are writing super long formulas, another added benefit in Coda is you can pop open a big window while you’re editing the formula. This gives your more whitespace to see where your formula goes:

Coda’s formula editor can be expanded

7. Detailed layout when expanding rows in tables.

You can already customize the look and feel of individual pages in both Coda and Notion. Embed images, dragn-and-drop elements, and multi-column layout are some of the common features for customizing pages. But what about tables?

The use case here is when you have a really wide table with a lot of columns. When you want to “view” a specific row in the table, you don’t want to force your teammate to scroll endlessly to the right to view all the data. When you “expand” the row, you want to see more details about that specific row in a user-friendly way. Here’s a comparison of what a detailed row layout looks in Coda and Notion using the “Customers” table example from bullet #1:

Coda’s detail layout for a row

Notion’s detail layout (page) for a row

The customizable row layouts allow you to view the columns in your table in a multi-column layout. If you have pictures in your table, those pictures can show up in the row layout as a “cover” image or a “profile picture” (in the use cases where you have a list of people). These custom layouts were modeled off of other popular websites that show lists of data like Yelp:

Quick custom layouts for viewing expanded rows in Coda.

Back to the “Customers” table in Notion, expanding the row gives you a detailed layout, but the most you can do in terms of customization is moving columns up and down:

Only move columns and up and down in Notion’s row detail layout

It’s small feature in the grand scheme of things, and Notion could probably easily add this feature in the future to their detailed row layouts. This feature may not be that interesting for the power users and makers out there since you’re familiar with the shape of your data and might be used to scrolling through your tables. This feature is geared towards teams where you need a way to show a ton of data to your teammates and:

Want to show the relevant columns to a teammate in a user-friendly way.
Don’t want to show all the columns from the table that don’t matter for the teammate’s job.

8. Sorting animation during Zoom meetings.

Sorting and filtering data in a table is pretty standard. How that data gets sorted is the feature that’s worth calling out in Coda. This is where a simple sorting animation makes your data alive as your teammates interact with the data Let’s consider this scenario:

You are leading a project retrospective and want to figure out what went well/didn’t go well last quarter with your team. You’ve set up a table for people in your meeting to add their ideas. In the Zoom meeting, you discover your teammate has already added a few of her ideas on what could go better this quarter to the table. These ideas end up getting looked at first which biases what people think about during the meeting. During the meeting, your other talkative teammate hogs the Zoom and rambles about their ideas for what didn’t go well last quarter. Most people in the Zoom are silent.

To even the playing field, people should be able to add their ideas and silently vote on them. This allows the best ideas to bubble to the top which have the most “votes.”

Coda’s voting table template is one of the simplest and most useful templates to use for synchronous meetings that happen over Zoom, Teams, etc. See the example below where a bunch of questions have been added. As the meting leader, you want to know which ideas are the most important to your teammates.

You’ll notice a slight animation as the table resorts and puts Idea #2 to the top of the table. Imagine 10, 50, or 100 people voting on ideas at once and seeing the table re-sort in real-time. This is exactly what Shipt did with their team retro template.

You don’t have to re-sort the table like you would in Google Sheets or Excel. This slight animation shows people on the Zoom meeting that stuff is happening during the meeting and encouraged to participate.

In Notion, sorting just kind of happens as values are being updated in your table. Going back to our “Customers” table, I’ve set up a sort on the Customer ID column to always sort from least to greatest:

Notion’s rows get sorted abruptly after you change values

As I change the Customer ID column number, the table gets sorted from least to greatest. The sorting happens abruptly. All of a sudden, you see Alice at the bottom and then Bob at the top. With a simple animation, the sorting feels more interactive and less jarring.

Worst case scenario: Small features like this make whatever tool you’re using during meetings a little more enjoyable and interesting.

Best case scenario: You get to hear what’s actually on the minds of your teammates since the feature encourages everyone to vote, participate, and get their “voice” heard.

9. Native forms (and support for them).

This feature is similar to bullet #4 on integrations and #5 on buttons.

The use case here is you want your teammates (or external vendors) to fill out a form to populate a table. That teammate or vendor most likely is not a Coda or Notion user yet so you just need a simple form that they can load on their computer or phone.

Native forms in Coda

Forms in Coda are a native feature which means our support team can help you troubleshoot any and all issues relating to creating and publishing your form. Prior to the forms feature, makers would create forms in Google Forms or Typeform and connect the responses to their Coda doc with Zapier. With native Forms in Coda, there’s no more overhead with setting up (and paying for) a 3rd party service like Zapier to connect things together. This is a simple form created in Coda with one question embedded (responses will show up in the Feedback page):

In Notion, there are 3rd-party add-ons for creating forms. Some of the popular add-ons include NotionForms, Form to Notion (Google Workspace add-on for Google Forms), and Chilipepper.

For most use cases on a small team, NotionForms’ Basic plan may be fine for your use case (it’s free). For bigger teams who need forms for multiple workspaces, you’ll need to sign up for their $15/mo plan. Again, for larger enterprises this cost is probably negligible. If you or your teammate wants to build out a complex form or run into issues with your form, who do you turn to for support? Someone from Notion’s customer support team may be able to assist you with the form. But since this is a 3rd-party add-on, you’re most likely going to contact the support team at NotionForms.

10. Cost savings on a team plan for bigger teams.

If you work for a big enterprise or well-funded company who doesn’t care about their SaaS and software bills, this bullet point might be irrelevant. For the other folks out there who are cost-conscious and making a considered decision about which tool to put through the gauntlet of your IT and Security teams, this bullet point might resonate.

Let’s take a look at the Team plans for both Coda and Notion (straight off the pricing page):

Coda’s team pricing plan

Notion’s team pricing plan

If we look at the true monthly cost (not the monthly cost after buying an annual plan), it’s Coda’s $36/user vs. Notion’s $10/user. That seems like a lot for Coda. But of course, it’s all in the details.

If you are just testing Coda or Notion out on a team of <10, I think Notion may actually be the more economical choice. Here’s a breakdown of what costs might look like for two types of teams:

Small team of 2 marketing ops leads and their 5-7 marketing colleagues
Multiple teams in a company of 200+ people where 75 people need to be able to create and edit pages and the remaining 100+ just need to make light edits or view stuff

Bottom line: The key distinction here between Coda and Notion is that in Coda, an editor has more powers than a guest does in Notion. A Coda editor can create pages, edit formulas, and do all the things a doc maker can do except for creating Coda docs. Most people in a company may not need to create Coda docs. Notion is inexpensive to use for small teams, but Coda is a more economical option in the long run for serious cross-functional and company-wide usage.

100 other features not mentioned.

As I stated at the top, I’m biased. There are hundreds of other features in Coda and Notion I didn’t mention. There are also features that make Notion better than Coda. It all depends on your team use case and how much time you want to put into bending the tool to do XYZ for your team.

For more unbiased reviews and feature comparisons, I’d consider taking a look at this Reddit collection from the r/Notion reddit (shout out to /u/ben-something) for compiling!).

While this Notion vs. Coda comparison is old (from 2020), this thread in the collection confirms some of the features I mentioned in the post (see this response from /u/makaike) like buttons, better formulas, and detailed row layouts. If you’re on Notion and thinking about trying Coda, check out the Notion Pack and this accompanying template!

Have any comments, questions, or corrections? Leave them in this Twitter thread.

Analyze your Peloton workout stats with real-time updates (template and scripts)

Al Chen — Fri, 15 Jan 2021 22:42:36 +0000

Now that gyms are closed, I’m using Peloton more and I love how Peloton tracks everything. But I always found it difficult to browse through previous workouts with the user interface on the tablet on the bike. What if I wanted to see summary stats just for 20-minute classes? What are my personal records for a given time period? I know they exist on the app somewhere, but I just want one place to visualize and analyze the data that matters to me.

I looked into how to get my Peloton data to do some custom sorting, filtering, and analysis. After a few weeks, I realized I could get automated updates to a Coda doc with all my Peloton stats. Read below to see how I was able to get these near real-time stats synced to Coda.

👉 Go straight to the template and copy it. The template contains this write-up as well as the tables and views you need to analyze your own Peloton workout stats.. Go to the Cycling page to see the cycling dashboard I’ve always wanted for myself. Use the Google Apps Script or Python script to start syncing your Peloton data (step-by-step instructions here). Video tutorial below:

See your workout stats on Peloton’s website

You’ve probably used the Peloton website before to view your workouts. Similar to the bike tablet, however, you have to click into each workout to see the stats for that individual workout:

If you click on the “Overview” and “Achievements” tabs, you’ll see some additional information about your workouts that might be useful. I just really wanted to see the stats associated with my bike rides, and those stats are “trapped” in each individual workout. You can’t see your output, calories, etc. over time.

Download a CSV of your workout stats

In the top right of the screenshot above, however, is a “Download Workouts” button. This lets you get a CSV download of all your workout data which is pretty sweet! This has most of the info I care about:

Some of the columns include Length, Type, Total Output, and Avg. Watts. This means you can start answering questions like:

What was my max output for 45-minute classes?
What was the max average cadence for each instructor I’ve taken a class from?
How has my average output been trending over time?

For many of you, this CSV should be enough for you to analyze your bike and non-bike workouts. You can throw this data into a spreadsheet (or a Coda doc 🙂), build some charts, and be on your merry way. Any time you want to get your most updated stats, just download the CSV and plug it into your spreadsheet or Coda doc.

For me, I wanted to see additional stats and automate the process of updating my stats. I don’t want to download the CSV and copy and paste into Coda every time I finished a workout.

Specifically, some questions I wanted to answer were:

Can I get stats like Difficulty (based on ratings submitted by Peloton members), Leaderboard Rank, and additional info about Peloton instructors?
How can I automate the process of getting my stats into a Coda doc without downloading a CSV?

Playing with the unofficial Peloton API

Turns out a few people have dabbled with the unofficial Peloton API. This one from Pat Litke is probably the most well-documented project. This library is great if you want to build a custom application on top of the Peloton API, but I wanted to find a way to simply get the data out from my Peloton account and into Coda.

Turns out there are quite a variety of endpoints off of https://api.onepeloton.com. You can get data about your Peloton account, instructors, and most importantly, your workouts. Here is a sample of data you can get about your account after authenticating with your username and password:

Some high-level data about your workouts:

After some more searching, there’s an API explorer on SwaggerHub showing all the endpoints you can ping on the Peloton API. One of the more interesting endpoints is api/workout/{workoutId}/performance\graph where you can get second-by-second metrics like output, resistance, and more:

With this data, you can build your own workout chart like the one you see on the bike tablet or on the website:

So I know the data is there, I just need to figure out which metrics I want and the charts and I want to see.

Picking the right Peloton workout metrics

At the very least, I wanted to get the metrics you get from the CSV output. So those columns are a given. The additional metrics and stats I wanted to get include:

Leaderboard rank — And total leaderboard users too
Difficulty — That crowdsourced rating from 1–10 you give after a workout
Total workouts — Total number of workouts others have done for this workout (gives you a sense of the popularity of the workout)
Total ratings — Should be correlated with Total workouts, but just interesting to see which workouts people provide ratings for
Instructor data — basically anything you can find on this Peloton instructors page

There’s a whole plethora of metrics you can get from the API but these were the ones I wanted to see in addition to the properties from the CSV. You can see every metric I pull in the All workouts page.

Main cycling dashboard

If you go to Cycling, you’ll see the main “dashboard” with the stats I wanted to see.

The 10 last rides is data you can get from the tablet or from the website, but it’s all in one place. Additionally, this list gets re-sorted every time new workouts get synced over from Peloton in All workouts. You can also filter the list by length of workout so that you are doing an apples-to-apples comparison. This is a nice condensed list to see how my workouts have been trending recently:

View of 10 most recent workouts on the Cycling dashboard

Below that 10 latest workouts table, I have a Personal Records table that just shows the best workouts I’ve had by _workout length._ Again, this is all data you can get from the CSV download, but the main difference is that this data gets updated automatically every hour, day, week, or whatever interval works for you.

Personal records by workout length

Finally, the charts below these tables show metrics over time so I can get a sense of how my performance have improved or declined over time.

Key views of Peloton workout stats

In sub-pages of the Cycling page, I sliced and diced my data based on different views I wanted to see my workouts. For instance, I’ve always wanted to see one view of my workouts grouped by workout length and then sorted by output (kj). Seeing the dates when I reached a really high output encourages me to work harder to get back to that level of performance. You can see this view in By length.

Another view I’ve always wanted to see is how my stats look grouped by instructor. Maybe certain instructors are more encouraging and therefore lead me to performing better. That’s what the By instructor page allows me to see. Looks like I’ve taken quite a few classes with Matt Welpers 💪.

Finally two other views I thought would be interesting for cycling workouts are:

By leaderboard rank — See your workouts sorted by your position on the leaderboard. These are stats that you can’t get in the CSV, so it’s neat to see which rides I’ve performed the best in relative to all other Peloton members.
Calendar — While there is a calendar on the tablet and website, this view color-codes the events by workout type. This means you can see other workout stats like running, strength, meditation, etc.

Two additional pages I set up to quickly sort and filter through Peloton data are:

Filter Workouts — Filter across all different dimensions
Browse Instructors — See the profiles for all your favorite instructors (this list also gets updated in the sync as new instructors are added)

Syncing data in “real-time” from Peloton to Coda

What good are these charts and tables if they don’t stay updated? Since we know the API exists, I wanted to find an automated process for keeping the workout data in my Coda doc up to date with my workouts on Peloton.

Caveats about Peloton API

Caveat #1: As I mentioned earlier, the Peloton API is unofficial and undocumented. This means Peloton (the company) has not “blessed” the use of their API for the public, so the API could change at any time. This would essentially break the scripts I’ve written to sync data over from Peloton to Coda.

Caveat #2: As with any API, you may use the API too aggressively and the fine folks at Peloton might rate-limit you. Don’t be the guy or gal who runs these scripts every minute thinking you need near real-time updates. In reality, a daily sync would probably be sufficient for most of you out there. Long story short, be nice and or else Peloton might block your use of their API 🚫.

Google Apps Script

If you’ve come across my other published docs, you’ve probably noticed I’m a big fan of Google Apps Script. It’s a low-code, scalable, and most importantly, free tool to connect your different tools together. Of course, Google makes it super easy to get data in and out of Google products, but you can set up workflows between non-Google tools as well.

The reason I think Google Apps Script is the right solution to sync your workout data from Peloton to Coda (as of the writing of this post) is because you can set up time-driven triggers. These are basically the rules for how often you want the script to run. As I mentioned in the caveats above, I would highly recommend setting a daily interval as the highest frequency to run the Google Apps Script.

Authentication with Peloton

This was probably the most difficult part of the script to figure out. In order to get your Peloton stats, you have to provide your username and password so that Peloton knows that you are, well, you!

In Google Apps Script, there is a UrlFetchApp class which lets you fetch any URL and convert the response to a JSON object. This part of the authentication code allows you to “login” to Peloton by submitting an options parameter which contains your Peloton login details:

var base_url = 'https://api.onepeloton.com'
var options = {
  'method' : 'post',
  'contentType': 'application/json',
  'payload' : JSON.stringify({'username_or_email': PELOTON_USERNAME, 
  'password': PELOTON_PASSWORD}),
  'muteHttpExceptions' : true
};
var login = UrlFetchApp.fetch(base_url + '/auth/login', options);

The problem : We need to make various other calls to the API and those calls won’t be authenticated. Peloton won’t just give you workout data for anyone just because you know their Peloton username. This is where the cookie for the login session comes into play:

var cookie = login.getAllHeaders()['Set-Cookie'];

The getAllHeaders() method returns all the header information from the HTTP response (which contains the cookie we need). We just store the relevant attribute in the cookie variable.

Another problem: The cookie object returned from the headers looks like this:

[" __cfduid=d88dc31d26154890903e4ed470ce2d2d31610584581; expires=Sat, 13-Feb-21 00:36:21 GMT; path=/; domain=.onepeloton.com; HttpOnly; SameSite=Lax", "peloton_session_id=57c891b1d8004745b7f19280a25af2cb; Domain=.onepeloton.com; HttpOnly; Max-Age=2592000; Path=/; SameSite=Lax; Secure; Version=1", "__ cfruid=14a3728b6c055fbcd9384022acdfdf91f2ec912e-1610584582; path=/; domain=.onepeloton.com; HttpOnly; Secure; SameSite=None"]

There’s all this expiration and domain attributes we don’t need. We just need the cfduid, peloton_session_id, and cfruid as one long string, so this part of the authentication function creates that string for us:

for (var i = 0; i < cookie.length; i++) { cookie[i] = cookie[i].split(';')[0] }
var authenticated_options = {'headers': {'Cookie': cookie.join(';')}}

Now we have this variable authenticated_options we can use for all our subsequent calls with the API so that Peloton knows we are still “logged in” to our Peloton account.

Getting existing Peloton workouts from Coda

The first time you run the script, all your workouts will get synced over to the Workouts table. To prevent unnecessary calls to the API, the script first pulls all the workout IDs from the Workouts table and compares it with the workout IDs returned from the API. We only look at the new workouts that need to be synced over to Coda since previous workout data won’t change.

Remember the api/workout/{workoutId}/performance_graph endpoint I mentioned earlier? Turns out that returns a ton of data, so we want to make sure we ping this endpoint only if we have to. This ensures we don’t pull performance graph data from Peloton we don’t need to and gives the Peloton API a break.

You can get workout data from the api/workout/{workoutId}/performance\graph endpoint and the api/workout/{workoutId} endpoint. You would think that the most detailed stats would be in the “performance graph,” but it turns out there is some data in api/workout/{workoutId} like leaderboard stats. So I created a workoutData object that contains “summary” and “performance” metrics.

Once we have all the relevant columns, we just format the data in a way that the Coda API needs and push that data to the Workouts table.

Python script

The Python script works pretty much the same way as the Google Apps Script, without the added benefit of being able to schedule the script to run at some interval in an easy way. Authentication, however, was much easier to accomplish with the Python script.

By importing the requests library, you get a user-friendly way to manage the logged-in session on Peloton. For instance, with these four lines of code, we can authenticate with Peloton so that for future calls to the API, we just need to use s.get() with various parameters:

s = requests.Session()
base_url = 'https://api.onepeloton.com'
payload = {'username_or_email': peloton_username, 'password': peloton_pw}
s.post(base_url + '/auth/login', json=payload)

There’s no messing around with 🍪, it just works.

Deploying a serverless function on Google Cloud

You could run the script on your local machine or on some public cloud platform. The tradeoff for having a more difficult method for automating how often the script runs is the flexibility with where/how you run the script.

Serverless functions were all the rage the last few years so I thought I’d try it out and see if I could spin up an example on Google Cloud Platform. The goal: get a URL that we can ping to trigger the Python script to run.

Setting up Google Cloud

Just setting up Google Cloud to get one Cloud Function to run was a bit of a pain. In addition to setting up a billing account (requires credit card) and tying a project to a billing account, you need to enable the Cloud Functions and Cloud Scheduler APIs (two services we need) to even start using Cloud Functions:

A helpful blog post I came across from a developer advocate at Google Cloud is this one. It’s a little harder to use since the blog post assumes you are using the gcloud command line tool. Nonetheless, Dustin creates a pattern for running the function that I wouldn’t have otherwise figured out (via Cloud Scheduler).

Setting up the Cloud Function

Once you start creating a Cloud Function, the key settings are to set the trigger type to “HTTP” and “Allow unauthenticated invocations.” I didn’t want to mess with IAM policies and all that stuff, so this should be fine for our needs:

Once you’re in the source code editor, you just need to set the runtime to “Python 3.7” and rename the Entry point to something like runPelotonSync. Then you can copy over the Python script to the code editor.

In order for this script to work with Cloud Functions, however, you have to wrap the script inside a function like this. Almost all the code goes into the pelotonData(request) function:

After that, hit Deploy and wait for Google Cloud to deploy your function. If everything works out correctly, you’ll get a unique “Trigger URL” on the “Trigger” tab of your Cloud Function:

Get your Cloud Function to run every day

Unlike Google Apps Script where you can simply tell Google how often you want your script to run on a dropdown menu, we have tell Google Cloud how often the python script should run via cron rules. We can set up the interval for calling the URL we got from our Cloud Function by using Google Cloud Scheduler.

The key field to fill out when you set up a new Cloud Scheduler job is the URL. This is the Trigger URL you got from your Cloud Function:

The Frequency is where you need indicate the cron rules for how often you want Cloud Scheduler to ping the URL. In the above screenshot, this job would run every minute. A helpful site to figure out what characters you should put for the cron rule is https://crontab.guru/. Once this is all set up, you can get detailed logging on your job and see manually run the job from the main Cloud Scheduler interface:

Final notes

I’m still playing around with this template for visualizing my Peloton stats and with the data that gets returned from the Peloton API. If you have any suggestions or see mistakes with the scripts, feel free to submit a PR here.

Hope these stats encourage you to hit new PRs whether your cycling, running, or doing a strength exercise.

Train hard, train smart, and have fun! — Matt Welpers

Interviews with entrepreneurs and digital nomads from Tim Ferriss' The 4-Hour Workweek

Al Chen — Fri, 20 Nov 2020 15:36:55 +0000

A few months ago I re-read Tim Ferriss' The 4-Hour Workweek and was curious what the people mentioned in the book think of the book today, if they still practice the principles from the book, etc. Many of the tools in the book are a bit outdated, but the concepts are still sound.

Decided to reach out to some of them and ended up conducting a variety of interviews with the entrepreneurs, digital nomads, and lifehackers in the book. tl:dr; Most people are still doing what they did in the early 2000s when Tim first met with them. Full story here: Uncovering the stories that make the 4-Hour Workweek possible: A 10-year review

How to build tasks dependencies with durations in Google Sheets, ClickUp, Monday, Wrike, SmartSheet, Notion, and Coda

Al Chen — Mon, 05 Oct 2020 02:41:55 +0000

This post originally appeared here.

Your project may contain tasks that depend on each other. Sometimes a task cannot start until its dependent (predecessor) task finishes, or maybe the task can start when its dependent task starts as well. As you manage tasks with these more complex dependency types (FS, SF, SS, FF), you may find more specialized software or SaaS tool to help you plan and visualize these tasks.

Goal of this post

This post doesn’t show the pros and cons of each platform, nor does it pick a “winner” that can handle this use case perfectly. Every team and project manager has their own unique use case and “jobs to be done.” My goal is to show how you can build and model the this list of requirements for your project’s tasks on each platform:

Task dependencies
Task end dates that factor in task durations
Task start dates that factor in the task’s dependent’s finish date (Finish-to-Start)
Automatic cascading of task dates when “kickoff” task date changes
Gantt chart and other visualization of tasks and dependencies

The way I found the different platforms and SaaS tools mentioned in this post was from simply Googling things like task dependencies software and best project management software task dependencies and seeing which tools showed up in Google Adwords and on “top 10 “ lists.

Jump straight to the video tutorial below. Open video in YouTube for timestamps to skip straight to a platform you’re interested in.

Coda template in the tutorial is here.

The above bullet points don’t capture every facet of tasks and dependencies. I think most of the platforms I describe below can handle a majority of these use cases. You’ll have to explore each platform on your own to see if the platform can handle:

Your specific use case of task dependencies
Additional use cases beyond task dependencies your team may expand to in the future

With that said, let’s start with the granddaddy of them all when you’ve just launched a project and reach for the first tool you have at your disposal: Google Sheets.

Google Sheets task dependencies

The list of tasks and dependencies in this Google Sheet will be the same list of tasks I try to replicate in every other platform. Copy the Google Sheet I show in this example if you want to use this for your own team.

Coming from an Excel/Google Sheets background, I found this task management template pretty easy to set up. With some clever uses of data validation, formulas, and conditional formatting, you can get close to how other dedicated project management platforms handle task dependencies and visualization.

Task dependencies

The Dependency column contains cells with the same data validation rules (dropdown list). The list of tasks you can choose is simply all of column A (your list of tasks). Here is what the settings look like for the data validation rules for each cell in column B:

Columns C, D, and E are just other attributes related to a Task Name. Task End Date just takes the Task Start Date and adds the values in the Duration (Days) column. It may not seem like a difficult formula, but you’d be surprised as to how difficult it is to get a proper date format after you’ve added a number (duration of task) to a date (task start date). You’ll see this crop up in the other platforms that don’t handle date and number format types well.

Dependency start and end dates

Dep Start Date and Dep End Date are VLOOKUP formulas that search the same table and return back columns 6 and 7, respectively:

Note how the lookup value is column B (each task’s dependency).

Finish-to-Start task start dates

To get each task’s Task Start Date to start when its dependent task finishes, we simply set the Task Start Date column equal to the Dep End Date column. This could lead to a recursive situation if you accidentally select a Dependency that is the same as the Task Name:

Cascading dates based on “kickoff” task start date

The “Instructor Shoot” task does not have any dependencies, and thus is our “kickoff” task. The Task Start Date for this task does not equal the Dep End Date column and is hard-coded. Therefore, when you edit that cell’s date, every other date will re-calculate or “cascade” down to reflect each task’s new start and end dates:

Like I said before, this is already a lot of great task dependency functionality with just a few formulas.

Gantt or timeline visualization of tasks

While a table view is great if you want to get specific dates for a task, visualizing these tasks on a gantt chart or some other visualization would be helpful. In order to build a simple visualization in Google Sheets, I put a number at the top representing each day of the month. You then write a formula in each cell below these numbers to see if that given task’s start and end dates day of the month falls within that range of numbers. If it does, then you just put an “x” in that cell. Here’s what that looks like:

You then copy this formula for all the cells (from columns K and beyond). Then you can apply some basic conditional formatting so that you can see each of the “x” more clearly. This results in a hacky gantt or timeline visualization of all your tasks. As you change the kickoff task start date for the “Instructor Shoot” task, you’ll see the hacky gantt chart shift as well:

ClickUp task dependencies

The List view in ClickUp looks similar to the Google Sheet (as in other platforms). You can sort by each column and there’s a very clear Status column for you to mark if the task is Open or Closed. This may be helpful when you want to hide all the closed tasks.

Task dependencies

In order to define each task’s dependency, you click on the 3 dots in the last “column” of the list and click on “Dependencies.” From there, you click on “Add waiting on task” to define which task this current task is “waiting for.”

The pop-up that shows the various aspects of the dependency shows some useful information regarding that task. For instance, the “Instructor Shoot” task is blocking three other tasks (this task is the predecessor or dependency for three other tasks).

This pop-up is helpful because it’s color-coded and gives you a chance to audit your task list to see which task may be blocking the most number of tasks. Of course, you’d have to manually go through each task to see which tasks that current tasks blocks. One could argue that the Gantt view lets you see this more easily (see more below).

I found this YouTube video pretty helpful in terms of explaining the importance of task dependencies overall rather than explaining how to use ClickUp itself:

One final note about task dependencies in ClickUp is that a warning pops up if you try to close a task that is still “waiting” on other tasks. You don’t want to close something before its dependencies are done, so there’s a ClickApp where you can toggle this feature on. It’s a nice warning to have for when you accidentally close a task you shouldn’t have:

Calculating task end dates and durations

As far as I can tell, you need to manually select each task’s start date. This means you won’t get that cascading feature we saw with Google Sheets. I tried creating a Formula column to calculate a task’s end date based on the tasks’ start date and its duration (in days), and the formula editor is pretty basic:

You can switch to the “Advanced Editor,” and start selecting other columns to create a more custom formula. My hunch for why this formula doesn’t work is because the Duration column is a number column format and you’re trying to add this to a Date column format.

Here’s the first instance where we see column formats getting in the way of writing a formula, which Google Sheets handles pretty seamlessly.

Gantt visualization

I’ve found that most project management platforms have pretty robust visualizations of your tasks and dependencies. They just work right out of the box. In ClickUp, arrows show which tasks are dependencies of other tasks with these arrows (which you can drag-and-drop):

For more complex projects, you can also toggle “Critical Path” and “Show Slack Time” to see this laid out on the gantt chart:

Monday.com task dependencies

My free trial of Monday expired before I could take screenshots 😃, so watch the video starting around 13:15 to see the walkthrough:

Task dependencies

This article gets you pretty far with setting up task dependencies.

I was able to get pretty close to replicating the functionality of Google Sheets, but ran into similar issues as ClickUp in terms of being able to calculate task end dates and task dependency end dates. My guess is that it also has something to do with the date formats mixing in with various other column formats in the Table view in Monday. The reason is I had to start using a Link to Item column type with the Mirror column type to “project out” the dependency’s start and end dates. Now you’re mixing dates, numbers, Link to Item, and Mirror column types which didn’t work out so well for the formula I was trying to build

As far as I can tell, you still need to set each task’s start date manually in order to get a calendar and gantt chart view that is useful for your team to look at. A nice error check (similar to the warning message when you close a dependency in ClickUp) is an Automation rule that prevents from you from setting a task start date before that task’s dependency is done. This type of error check doesn’t need to exist in the Google Sheet because all the task start dates are formulaic and are dependent on each task’s predecessor.

Wrike task dependencies

The table view in Wrike looks similar to ClickUp and Monday, but is starting to feel a little more spreadsheet-y. I didn’t include all the columns from the Google Sheet for simplicity’s sake. I easily copied and pasted from the Google Sheet into the table view in Wrike to build a similar looking table as Google Sheets. You’ll notice the different “tabs” at the top to quickly switch to a different view of your task list:

Task dependencies

Unlike Google Sheets, ClickUp, and Monday, Wrike takes a more traditional project management approach to defining a task’s dependency or predecessor. You’ll notice in the Predecessors column there are numbers and text like “2FS’ and “4F.” The number represents the row of the dependency and the “FS” is “Finish-to-Start” dependency type.

You actually can’t edit the Predecessors column in the table view, only in the Gantt Chart view here:

Similar to ClickUp, you can drag-and-drop the bars in the Gantt chart view to change the dates of a given task and draw arrows to other tasks to create new dependencies:

Cascading dates based on “kickoff” task

While you can pinpoint the dependency with the row number for each task and whether it’s a “FS” or “SF” dependency type, I don’t believe the start dates and durations cascade “down” similar to the functionality I built in Google Sheets. Based on this help article, it appears the only way to do this is to hold the SHIFT key to move an entire “dependency chain” forward or backward in time. I think having a WYSIWIG editor on the Gantt chart view to achieve this same functionality would be a nice to have.

I was able to create this Calc Due Date column which simply takes the task’s Start Date and adds the Duration to it. The Calc Due Date column is a custom field that is a _Formula _type:

Smartsheet task dependencies

Smartsheet is one of the more well known dedicated project management platforms out there. Their software looks and feels more like a spreadsheet than the other previous platforms (the “sheet” in their name might have been an indication). The grid view looks just like a spreadsheet with some “sticky” columns to the left for attachments, comments, and other attributes for a given task.

Task dependencies

To incorporate dependencies for each task, you have to go to the Project Settings and explicitly enable Dependencies. Through these settings, it’s clear that Smartsheet has iterated over the years to provide users with a defined set of options when it comes to setting dependencies.

This has its tradeoffs. On one hand, you have this very powerful piece of software that does task dependencies really well and you know that they have thought about all the edge cases related to dependencies and due dates. On the other hand, you lose some flexibility (that you get with a Google Sheet) in terms of defining your own dependency “rules” and “project settings.” This decision to go with an opinionated piece of software like Smartsheet versus building something yourself goes beyond project management and task dependencies.

Ok rant over 😃. Let’s get back to Smartsheet. Once you’ve enabled dependencies, you need to select the column for your Predecessor as well as the column for Duration. I already have a column for Duration (which came from the Google Sheet) and I created a column called Dependency. This help article also walks you through how to work with dependencies in Smartsheet in detail.

Similar to Wrike, you put the row number in the Dependency column for the task that is the dependency for that current ask. To get more specific with each dependency, you can click on the pencil icon to open up more settings for that dependency. Here is where you can define a more specific dependency type (e.g. SF, FF, SS):

Cascading dates based on “kickoff” date

Unlike Google Sheets (or any of the other dedicated project management platforms so far), I didn’t have to create separate columns for a dependency’s start date or end date. Smartsheet automatically re-calculates the Task Start Date and Task End Date based on the date you select for your “kickoff” task (in this case “Instructor Shoot”). This is a nice built-in feature that removes the need for you to do a VLOOKUP in Google Sheets or struggle with creating a custom formula in ClickUp, Monday, or Wrike:

You also get an error message if you try to change the Task Start Date for any task that has a dependency. We see similar error checks in ClickUp and Wrike:

Gantt chart visualization

The gantt chart view is pretty similar to the other project management platforms. You can’t adjust the task start date (left side of the bar in the gantt chart) because you’ll run into the same error as beforeーmessing with a task’s start date means it will break the “chain” of dependencies/predecessors. The view doesn’t look that far off from the hacked up chart I was able to build in Google Sheets:

Notion task dependencies

Tables in Notion act similar to the list/table views in ClickUp, Monday, Wrike, and Smartsheet in that the columns have specific types. This will lead to issues down the line because I try to mix column types in the formulas I write. Aside from this limitation, the table looks and feels like a spreadsheet and I was able to easily copy and paste into the table from Google Sheets:

Task dependencies

The Dependency column is a relation column type in Notion. This column type lets you “lookup” to other tables in your workspace, but you can also lookup to the same table. This is pretty similar to the data validation in Google Sheets. I can now select any task from the first column as the Dependency in the second column.

Task and dependency start and end dates

Notion also has a formula language and I was able to create a Task End Date similar to what we have in Google Sheets. It’s simply Task Start Date plus the Duration. You can use the dateAdd function in Notion to make this work:

In the Google Sheet, the Task Start Date needs to be built off of the Dep End Date column in order to get the “cascading” date effect when you select the date for the “kickoff” task. I started building out the Dep Start Date and Dep End Date columns by using a Rollup column type. This allows you to “project” the task start and end dates for a given dependency:

With the Dep Start Date and Dep End Date, we’re getting close to mimicking the Google Sheets structure. Then came the issue of trying to build the formula for the Task Start Date. In the screenshot below, you’ll notice that there’s a “Type mismatch” error which leads me to believe that the Rollup column type in the Dep End Date column is messing up the formula:

This formula looks to see if there is anything in the Dependency column. If that column is blank (which is the case for the “Instructor Shoot” task), then it just shows the value in Task Start Date. This is the date we want to use to “cascade” the dates down the table. If the Task End Date column was a date format type, I think this formula would work since that column affects the Dep End Date column. I tried looking at a few articles discussing date functions here, here, and here, but couldn’t figure out a solution. If you find one, let me know!

After writing into support, I found out the Task Start Date formula I’m trying to write could potentially lead to a recursive loop situation, and that Notion formulas don’t currently support this type of use case. Similar to ClickUp, Monday, and Wrike, it looks like you have to manually enter in each task’s start date to build a proper view of your project. This YouTube video below also shows how to build dependencies which resembles the “waiting” and “blocking” concept in ClickUp:

Calendar visualization

I believe there is a gantt charts are still on Notion’s roadmap, so the only native view that would make sense for this project is the Calendar view. You can easily switch to this view by clicking the dropdown near the top-left of the table:

Coda task dependencies

Disclosure: I work at Coda.

Tables in Coda are similar to the “list” view in ClickUp, Monday, Smartsheet, and Wrike in that columns have specific format types. If you accidentally put a number in a text column, however, it’s not the end of the world and Coda won’t give you an error message. Formulas in Coda tables also get applied to the entire column (not just that specific row like in Google Sheets). You can see the actual template in Task dependencies template or follow the screenshots below.

Task dependencies

Similar to Notion’s Relation column type, Coda has a Lookup column type that lets you “lookup” to another table in your doc or to the current table the column is in. In this case, the Dependency column is a lookup column to the “All Tasks” table:

Now I can select any task in the Task Name column as the dependent task in the Dependency column. Also similar to Notion, hovering over each value in the dropdown actually lets you see all the data related to that specific task:

Task/dependency start and end dates

In some of the project management platforms described above, writing formulas led to errors due to inconsistencies with column types. Notably, being able to add a number (from our Duration (Days) column) to a task’s start date is problematic since you are mixing date and number formats. Smartsheet handles this well given that it has strict rules around dependencies and task durations. Notion also is able to handle this scenario with their dateAdd function.

In Coda, the Duration column in our table is actually a custom duration column type. This means if you enter the number “3” in this column, the column will automatically convert that number to “3 days.” Therefore, adding Task Start Date with Duration yields another date as shown in the formula below:

If you recall in the Google Sheet, were able to add the task’s start date with a number and Google Sheets automatically converts the number to the “number of days” that task is supposed to take. No issues with mixing and matching column formats. Since Coda formulas apply to the entire column, you can use the name of the column in the formula instead of a specific cell reference (e.g. “D5”).

To get the dependency’s start date and end date like we have in the Google Sheets, we can simply reference the Dependency column and “project out” it’s start and end dates like so:

The Dependency column contains the entire “row” of a given task in the first column of our table, so each column in this table is bound to whatever task you select in the Dependency column. This is similar to the Google Sheet where we do a VLOOKUP onto the same table to find the dependency task’s start and end dates. One could argue that the Coda formula is a bit easier to read since you don’t need to input a bunch of values or references into the formula

Cascading dates with a “kickoff” date

Coda accounts for potential recursive situations, so you’re able to create this “cascading” list of dates similar to the functionality we have in the Google Sheet. In this case, I give the user the ability to input their own start date for the “kickoff” task (”Instructor Shoot”) as a date picker right above the table:

This date picker is a control which you can assign a name to (similar to defined named ranges in Google Sheets). I named this date picker firstTaskDate which we’ll use in a formula in our table.

When I select new dates in the firstTaskDate date picker, you’ll see all the task and dependency dates automatically re-adjust based on the start date of the “Instructor Shoot” task. This is similar to adjusting the first hard-coded start date in Google Sheets, moving the “dependency chain” in Wrike, and shifting the gantt chart in Smartsheet:

This all works because of the formula in the Task Start Date column. This is a formula I was not able to replicate in other dedicated project management software due to a mismatch in column format types and the potential for a recursive formula. The formula is an IF statement that checks to see if the Dependency column is blank. If it is, then it uses the firstTaskDate date picker value right above the table as the date for the “kickoff task.” Otherwise, it just takes the value in the Dep End Date column. We are modeling a simple Finish-to-Start scenario but we could easily write a more custom formula to account for SF, SS, and FF dependency types.

The formula in Google Sheets is easier because we can simply reference the Dep End Date column without having to write an IF statement. But this comes at the cost of giving the user a clean user input to pick the date for the “kickoff” task. In Coda, it’s a date picker that goes right above the table with some helper text before it. In Google Sheets, you would have to format the hard-coded cell and perhaps put in a note for the user to know that this is the specific cell that the user needs to edit:

Gantt and calendar visualizations

Similar to other platforms, you can visualize a list of tasks and dependencies in different ways that make sense for how your team operates. The gantt chart view is not as robust and feature rich as ClickUp, Monday, Wrike, and Smartsheet. You cannot draw arrows from one task to another nor can you adjust task lengths by simply dragging and dropping the right and left boundaries of a bar on the chart. The reason for this is because each task’s start and end dates are fixed via formulas, so the tradeoff here is being able to have the “cascading” ability of dates (like you have in Coda and Google Sheets) versus the flexibility of editing each task’s dates one-by-one (as you have in ClickUp, Monday, and Wrike):

It’s a pretty bare bones gantt chart, but similar to other platforms you can hover over each bar and see all the data related to that specific task. When you adjust the date of the “kickoff” task, you’ll see the gantt chart automatically adjust because all these views are tied to each other.

The calendar view is pretty similar to all the other tools and similar to the gantt chart, is connect to your main table of tasks and dependencies:

Conclusion

There are many other SaaS tools, platforms, and project management software I did not cover in this tutorial. Notably, Microsoft Project is something that came up during my research that I know many project management professionals have used for years to manage tasks and dependencies.

As I stated the top of this post, there is no “winner” that you can walk away with from this tutorial. Unlike other “top project management software” blog posts where you get a cursory view of each software’s pros and cons, this post explores the idiosyncrasies of task dependencies which may not even be relevant to your project. If you’re in the process of researching software and you need the software to accomplish a very specific task for your team, you’ll unfortunately have to dig deep into the software to see if it does what you want it to do. Entire teams at large companies are dedicated to vetting these feature sets because once you’ve selected the software, the switching costs can be very high.

If you’ve read this far, hopefully I’ve helped you comprehend a nuance about each platform in relation to task dependencies. You’ll have to do additional testing on each platform to figure out if the platform is able to accomplish some other specific task related to how your team operates and the requirements of your project.

How to export and analyze Jira issues in Google Sheets vs. Coda

Al Chen — Fri, 04 Sep 2020 19:46:53 +0000

Once your Jira backlog of issues grows to a certain point, you may want to view all your issues in another format like a spreadsheet to figure out what your team should focus on. In a spreadsheet, you can quickly filter, sort, and organize issues by multiple dimensions like priority, due date, and status. The issue is that the spreadsheet is only a snapshot of your Jira issues. As your team makes updates in Jira, those updates to sync to your spreadsheet unless you use an add-on like Jira Cloud for Sheets. An alternative is to use a Jira Packs table in Coda to sync your data from Jira in a table format.

In this tutorial, I’m going to show you how you can do a regular export of Jira issues into Google Sheets and how to analyze issues once they’ve been exported. I’ll also show how you can do a similar type of export in Coda except the issues in Coda will stay synced with your Jira project.

Skip straight to the video tutorial below and check out this template for the part of the tutorial focused on syncing Jira issues into Coda: https://coda.io/@atc/jira-backlog-template-for-tutorial

Exporting Jira issues to Google Sheets

This is assuming you are using the hosted version of Jira. Before you can export, you need to create a filter of your issues for your project. Under the Filters menu, click on View all filters and you’ll see a Create filter button to the right:

After you’ve created the filter for your project, you’ll see an Excel and Google Sheets icon near the top of the screen:

This will give you a clean export of your Jira issues. An important note:

This export only gives you a snapshot of your Jira issues. Any updates you make in Jira will not get reflected in Google Sheets and vice versa.

Your new Google Sheet will contain two worksheets: About and Your Jira Issues. The About page just contains some images about using Jira Cloud for Google Sheets (useful if you want to keep data in-sync from Jira to Google Sheets). The Jira Query shows up on this worksheet which is useful if you are using Jira Query Language (JQL) to query your Jira project and/or using the Jira Cloud for Google Sheets add-on.

Playing with your Jira issues in Google Sheets

Getting a tabular view of your Jira issues is really useful since you can see all the fields for an issue organized along the top of the spreadsheet. A logical next step here is to filter your list of issues by a field like Status. Here, I’m setting up a filter to see all issue that have a Status of “To Do.” You could of course apply multiple filters depending on what you want to see (all issues with a Status of “To Do” and Created on 8”/16/20”).

Dealing with teammates who want to filter Jira issues

The issue with doing a basic filter like this is that your teammate may want to see the issues assigned to him or her and perhaps color those rows a certain way. Every time your teammate jumps into the Google Sheet and applies their own filters, this will “overwrite” your own filter that you applied.

This is where Filter Views in Google Sheets come into play. You can tell your teammates that once they have filtered the list of issues to their heart’s desire, have them create a Filter View by clicking the Data , Filter views , then Save as filter view. Below, I have filtered the list of issues to Status of “To Do” and the Assignee is “Polly Rose.” I then create a filter view called “Polly’s issues”:

Now, if a teammate goes into the Google Sheet, they can click on Data , Filter Views , and see a list of filtered views created. They can then pick the filtered view they have created without messing up anyone else’s filters. When you close out the filtered view, the list just goes back to the full list of unfiltered Jira issues.

Creating copies of your issue list into multiple worksheets and keeping data synced between worksheets

This list of filtered views could grow quite large, so another option is to create multiple copies of your list of issues and perhaps each worksheet is meant for a specific teammate, only contains issues of a certain Status, or some other field. It depends on the structure and needs of your team.

The big question is this: does the list of issues need to be synced across worksheets in your Google Sheet?

If the purpose of exporting your Jira issues to Google Sheets is to report and analyze issues, then this question is not important. My guess is most people are exporting into Google Sheets for this purpose, and doing the actual “work” of updating and logging issues in Jira directly. However, the power of the spreadsheet is that you can add columns and with custom formulas that calculate certain metrics off or your Jira issues.

You could create a formula to calculate the number of days between when the issue was reported and when the issue was last updated. Or a formula that groups issues into the Assignee’s team which means you have a table somewhere in your Google Sheet mapping team member to team (and you do a VLOOKUP to the team name).

With the above scenarios, you’ll have to find a way to keep your worksheets in sync with each other so that formulas get applied to all worksheets and not just one worksheet where you’ve written the formula. A common solution to this: use the QUERY function in Google Sheets so that each copy of the main list of issues is not a direct copy, but rather a real-time query of the main list of issues.

The syntax looks very similar to SQL, and in this case assume the “Your Jira Issues” worksheet is the main table of issues that won’t be changed. The columns A:L are simply the column in this worksheet that contain my data, but you may have to expand the column reference if you continually add more custom formulas to your main issue list.

Grouping your issues in a PivotTable

One final strategy for viewing and analyzing your Jira issues is by creating a PivotTable of your Jira issues. This strategy is similar to the QUERY function in that the PivotTable will always pick up any new data that is added to the main list of issues. I don’t think this is a common scenario since this is just a snapshot of your Jira issues, but you may have a recurring task of exporting Jira issues into Google Sheets which leads to new issues getting added into Google Sheets.

This use case of PivotTables is non-traditional since I won’t be aggregating any dimension in the “Values” of the PivotTable. The PivotTable could be interesting if you want to see how Story Point Estimate, for instance, changes depending on how your pivot your data. All I’m doing below is pivoting by Status so I can see all my issues grouped by a certain status:

To get this view of my Jira issues, I’m only adding dimensions to the “Rows” in the PivotTable and unchecking the “Show totals” box so I can get a clean view of the issues.

Exporting and syncing Jira issues into Coda

While I could do a simple copy and paste from the Google Sheet into a Coda table, the main benefit of Coda is this:

Updates in Jira will get synced into a table in Coda

As mentioned above, there is a special type of table called Packs tables, which are tables that allows you to visualize tabular data from popular tools like Jira, Gmail, and Google Calendar in real time. In this case, I’m creating a Jira Packs table:

When you click on the Options in the table, there are a variety of settings that let you control how often data is synced over from Jira and what data is actually synced over:

Some options to choose from:

Refresh rate: Manual or every day/hour
Table limit: How many rows to sync over (may want to limit if you have thousands of Jira issues)
Project name: Sync over issues only from a specific project
Include sub-tasks: Jira issues can be broken out into sub-issues, so know the parent or child of an issue may be important for your workflow
JQL Query: Further customize exactly what issues get synced over with JQL

Adding additional attributes from Jira issues to Coda

Each issue in the Jira Packs table is a rich reference to the actual issue in your Jira instance. When you hover of an issue, you can see all the other attributes for that issue regardless if that attribute has a value. You can add those attributes to your Coda table in case you decide to use that attribute in the future in Jira:

Visualizing Jira issues in Coda

Once the data is synced over, you can do spreadsheet-like things with the table. The most powerful feature for most teams’ workflows is creating views off of the main table of Jira issues. This is similar to creating multiple worksheets of the same list of issues in Google Sheets. The difference is that Coda tables and views are always connected, so any changes in one view or in the main table are automatically propagated to all other connected views. This prevents the need to use QUERY in Google Sheets or some other workaround to keep data synced between worksheets.

This view below is built off the same list of issues but grouped across two dimensions: Assignee and Status. You can see this view of Jira issues in this page of the template.

Depending on who you select as the Assignee, a chart below updates showing the Issues and Story Point Estimate for that person:

Custom columns built off of Jira issues

In the Planned completion column, I want to communicate to the broader team when this issue might get fixed. Adding a custom field like this to your Jira project can be complicated, and in Coda it’s simply just adding another column. There is no formula in this column, but rather a value (eg. Q4, Q1, Q2) that my team has added after prioritizing and grooming our issues list.

Once I have this column in place, I can group by this column (think PivotTable) and completely change the layout of how my data looks to a kanban board. This is another view of the same list of Jira issues but now I can move issues around from one list to another depending on when I expect the Planned completion date to be:

Jira issues are in sync. Always.

The key takeaway is that my team can continue working in Jira, and any of these custom views, columns, and formulas will update automatically. Certain teams or external stakeholders may need to see Jira issues “rolled up” into some report or analysis, and Jira doesn’t provide the exact reporting you need. Those team members can see Jira issues the way they want in Coda.

How to extract data from tables in multiple Google Docs and combine into one table in Coda

Al Chen — Wed, 19 Aug 2020 15:53:03 +0000

If your organization creates multiple Google Docs every day and you want a better way to sort, filter, and organize all these Google Docs, this solution may be useful for you. This tutorial walks you through how to extract the important metadata from a table in a Google Doc and sync that data into a table in Coda.

I often see templates for project briefs, meeting notes, or candidate interview as Google Docs at other organizations which are duplicated over and over again. These Google Docs are then stored in Google Drive which can get very unwieldy to manage unless someone is consistently organizing the folders in Google Drive. Another common trait in these Google Docs: a table near the top of the Google Doc summarizing what the doc is about.

A user recently needed a solution for extracting the data from the tables in their Google Docs into a table in Coda, so I wrote this Google Apps Script to solve the problem.

If you want to skip right to using the Google Apps Scripts, go to this gist. Here is a video tutorial as well:

Why sync data from a Google Doc to a structured table?

If the summary table in your Google Doc contains all the relevant “metadata” for your Google Doc, why not extract that data into a main table where you can better sort, filter, and organize all the Google Docs your organization is creating? This gives you a “catalog” of all the various project briefs, meeting notes, and candidate interview Google Docs your team is creating.

Alternatively, you could just use one Coda doc to manage multiple project briefs, but let’s assume your organization is still accustomed to using Google Docs as the main “data storage” tool for these lightweight use cases.

Features

This Google Apps Script does the following:

Checks a Google Drive folder for any new Google Docs that have been added to the folder
Takes the first table in the Google Doc and extracts the info
Sends the data to a row in a Coda table along with the Google Doc link
Accounts for Google Docs that are shortcuts to a Google Doc owned by someone else (the Google Doc was shared with you)

Setting up the script

From line 9 to line 17 of the script, you’ll need to enter in some of your own data to make the script work. Step-by-step:

Go to script.google.com and create a new project and give your project a name. Delete everything in the editor that shows up by default (e.g. myFunction stuff).
Go to Libraries then Resources and paste the following string of text/numbers into the library field: 15IQuWOk8MqT50FDWomh57UqWGH23gjsWVWYFms3ton6L-UHmefYHS9Vl.
Click Add and then select version 9 of the Coda library to use (as of August 2020, version 9 — Coda API v1.0.0 is the latest)
Copy and paste the entire script into your Google Apps Script project and click File then Save.
Go to your Coda account settings, scroll down until you see “API SETTINGS” and click Generate API Token. Copy and paste that API token into the value for YOUR_API_KEY in the script. Note: do not delete the single apostrophes around YOUR_API_KEY.
Get the the doc ID from your Coda doc by copying and pasting all the characters after the _d in the URL of your Coda doc (should be about 10 characters). You can also use the Doc ID Extractor tool in the Coda API docs. Copy and paste your doc ID into TARGET_DOC_ID.
Go back to your account settings and scroll down to the very bottom until you see “Labs.” Toggle “Enable Developer Mode” to ON.
Hover over the table name in your Coda doc and click on the 3 dots that show up next to your table name. Click on “Copy table ID” and paste this value into TARGET_TABLE_ID.
In your Coda table, create column names that match exactly the column names from the table in your Google Doc (pay attention to upper and lower case).
In your Coda table, add a column called Google Doc Link.
Get your Google Driver folder ID and paste it into the GOOGLE_DRIVE_FOLDER_ID variable (the ID is everything after drive.google.com/drive/folders when you’re viewing your folder in your browser).
Run the script by clicking “Select function” on the Google Apps Script toolbar, select runSync, and hit the ▶️ button.
To get the script to run every minute, hour, or day, click on the clock 🕒 button to the left of the ▶️ button to create a time-driven trigger.
Click Add Trigger, make sure runSync is set as the function to run, “Select event source” should be Time-driven, and play around with the type of time based trigger that fits your needs. I like to set the “Failure notification settings” to Notify me immediately so I know when my script fails to run.

Data is trapped in your Google Doc tables

In order for this script to work, the table in each of your Google Docs needs to be structured like this:

Notice how the column “headers” show up as rows and the value of each “header” is in the second column. This is not the traditional way you would use tables in a spreadsheet. The reason you might have the metadata about the Google Doc stored in this structure is because it’s easier to add new headers to the rows when the values for each header can be very long. If this was structured like a regular table, the column headers would be really narrow and adding more columns would make the data too squished.

In the above example, Executive Producers would take up a lot of room if this was laid out like a regular table. By organizing data with the properties in the first column and the values in the second column, it makes it easier to view the data easily in a structured format.

This data, however, is “trapped” in your Google Doc. If you want to see all the Google Docs that have a certain Genre or sort all the Google Docs by the Original Release date, this is not doable in Google Drive. With Google Apps Script, we can tap the Google Drive API and Google Docs API to set this data free 🙌 .

How the script works

As part of set up process, you need to create a table in a Coda doc that has the same properties in your table in your Google Docs. An important caveat:

The property names in the first column of your table have to be consistent across _all_ your Google Docs.

This just ensures that all data is synced over correctly to Coda. If you want additional properties synced, you have to add those columns to your Coda table. The spelling of the column names have to also be consistent with your Coda table:

Notice that you also have to add an additional column called Google Doc Link to your table. This will store the link to the Google Doc once the data is synced over to Coda.

You can call this column something else, but make sure you replace the variable name in Line 14 of the script with the new name of this column:

Google Docs in your Google Drive folder

All the Google Docs that contain a table that you want to sync over to Coda should be stored in the same Google Drive folder. Don’t store other types of files in this folder except for the Google Docs you want to sync over the data from the table.

This script also works with Google Docs your colleagues have shared with you that you’ve added to your Google Drive. For instance, in the above screenshot “Doug Doc” is a shortcut to another doc somewhere else. You’ll now it’s a shortcut when it has that little arrow icon overlaid on the doc icon:

You can create these shortcuts (and rename the shortcut) when you add someone else’s doc to your Google Drive:

Run the runSync function

The main function to run in the script is the runSync function. This function checks all the current rows in your Coda table that contains existing data you’ve synced over and compares the Google Doc link with what’s currently in your Google Drive folder. If new docs get added to the Google Drive folder, the script only syncs over the data from the tables in those new docs. This leads to another caveat:

If you update existing Google Docs that have already been synced over to the Coda table, those new updates won't show up in Coda.

To get around this, you can simply delete the corresponding rows in your Coda table so that new updates make it over to your Coda table on a fresh sync.

Once the data is synced over, you can change the column formats in your Coda table so that it reflects the right format of your data:

What’s neat is if you’ve shared your Coda doc with certain people already, those people will automatically show up as a People column format in your table. In the above screenshot, the Who Picked column is a People column format, and the original data from the Google Doc table was just a name. When the name “Adam Davis” was added to Coda, however, Coda automatically finds the right person you’ve shared the doc with and makes it a selectable option in the dropdown. Now that you have this person reference, you can view other data about the person like their email address (and send them messages with the Gmail or Slack Packs).

Getting current Google Doc IDs from Coda

The runSync script calls a few other functions in order to make the sync work. The currentFileIds function uses the Coda API to get all the current Google Docs you’ve already synced over. Remember the Google Doc Link column you added to your Coda table? This function looks at that column to see what Google Docs have already synced over.

Getting current Google Drive files

Now that we have the current Google Doc IDs from your Coda table , we need to compare this with what’s actually in in your Google Drive folder. The getDriveFiles function uses the Google Drive API to get all the files from the folder you’ve identified in the GOOGLE_DRIVE_FOLDER_ID so that we can see what new Google Docs need to be synced over to Coda.

As I mentioned above, you don’t have to own the Google Doc in order to sync over the data from the table in the Google Doc. As long as you’ve added a shortcut to the Google Doc to Google Drive and that shortcut exists in your Google Drive folder, the getTargedId function figures out if that file is a shortcut or not.

Extracting the data from a table in a Google Doc

The data is “trapped” in your Google Doc table. The getRows function utilizes the Google Docs API to pull that data out of your Google Doc and stores it in a format that’s appropriate for Coda. Another important caveat about the tables in your Google Docs:

The table needs to be the **first** table in your Google Doc.

If you have multiple tables in your Google Doc and the table with the metadata about your Google Doc is not the first table, then the script won’t work. If for some reason you know that the table you want to sync over to Coda is the third table in all your Google Docs, you can change the index in line 60 of the script to:

var table = body.getTables()[2]

The number in the bracket should always be one less than the actual number table in your Google Doc (e.g. if it’s the fifth table, you would put 4 in the brackets).

Another caveat regarding the data that you sync over:

You can't sync over hyperlinks from your Google Doc table to Coda.

This means instead of writing Google (where the word “Google” is hyperlinked to “google.com”), you’d have to write out the full link like this: “Google: www.google.com”. There are ways to get hyperlinked text to show up in Coda, but the script would’ve gotten much more complex.

Adding the metadata to Coda

The final step is adding the data to Coda. The addRowToCoda function uses the Coda API once again to add the data from your Google Doc table as a row to your Coda table. Now that the data is in a Coda table, you can start filtering, sorting, and building views off of this data so that you can easily find the Google Doc you’re interested in.

Final words

This script solves a specific niche scenario of adding data from tables in Google Docs to a table in Coda. If the data in your table is structured like a “normal” table with columns along the top, then you’ll have to edit the script to account for this structure:

You could also sync the data to Google Sheets using the Google Sheets API if you’re not ready to use Coda yet.

How to sync data from Coda to Google Sheets (and vice versa) with Google Apps Script tutorial

Al Chen — Mon, 04 May 2020 17:56:22 +0000

Two new scripts

Last year I published a tutorial on how to sync data between two Coda docs and data between two Google Sheets. What was missing from the tutorial was how to sync data between a Coda doc and a Google Sheet. Writing these scripts was definitely more challenging than the original script I wrote for syncing two Coda docs since the data model for Coda is different from Google Sheets. Please read the caveats below about these scripts to learn about some of the roadblocks I encountered when writing these scripts.

If you are reading this, chances are you have a lot of experience with Google Sheets, Coda, and perhaps the Coda API. I’m going to skip the introduction to Coda as I did with the last tutorial and get straight to the point on how you can:

Sync data from Coda -> Google Sheets
Sync data from Google Sheets -> Coda

If you want to skip right to using the Google Apps Scripts, go to the other two pages in this doc (mentioned above) or go to this repo which contains all four scripts for syncing data (PRs welcome). Here are two video tutorials if you prefer a visual tutorial.

Coda to Google Sheets

Google Sheets to Coda

Features

There are some limitations to the scripts which I’ll discuss later on in this blog post, but these are the main features for each script:

Coda -> Google Sheets

New rows that get added or deleted in your Coda table will also get added or deleted in Google Sheets
Existing rows that get updated in Coda will also get updated in Google Sheets
You can re-arrange the columns in your Google Sheet and the sync will still sync the appropriate columns in your Google Sheet
You can add or insert new columns in your Google Sheet and write formulas in these new columns
You can add or insert new columns in your table in Coda and these columns won’t get synced to Google Sheets (unless you create a new column in Google Sheets with the same column name as the one in your Coda table)

Google Sheets -> Coda

New rows that get added or deleted in your Google Sheet worksheet will also get added or deleted in your Coda table
Existing rows that get updated in your Google Sheet worksheet will also get updated in Coda
You can sort and filter the rows in your target Coda table and the script will still add, delete, and update the appropriate rows in Coda
You can add rows to your Coda table and not get them deleted on the sync by adding a “Do not delete” checkbox column in your Coda table that is set to true (more about this later in the post)

Some of the features in the Coda -> Google Sheets script also apply to the Google Sheets -> Coda script, but I haven’t fully tested every use case. If you see any bugs, please add them to the repo’s issues list.

Setup: Coda to Google Sheets script

Starting in line 9 to line 14 of the coda_to_sheet.js script, you’ll need to enter in some of your own data to make the script work. Step-by-step:

Go to script.google.com and create a new project and give your project a name.
Go to Libraries then Resources and paste the following string of text/numbers into the library field: 15IQuWOk8MqT50FDWomh57UqWGH23gjsWVWYFms3ton6L-UHmefYHS9Vl.
Click Add and then select a version of the library to use (as of May 2020, version 8 is the latest)
Copy and paste the entire script into your Google Apps Script project and click File then Save.
Go to your Coda account settings, scroll down until you see “API SETTINGS” and click Generate API Token. Copy and paste that API token into the value for YOUR_API_KEY in the script. Note: do not delete the single apostrophes around YOUR_API_KEY.
Get the the doc ID from your Coda doc by copying and pasting all the characters after the _d in the URL of your Coda doc (should be about 10 characters). You can also use the Doc ID Extractor tool in the Coda API docs. Copy and paste your doc ID into YOUR_SOURCE_DOC_ID.
Go back to your account settings and scroll down to the very bottom until you see “Labs.” Toggle “Enable Developer Mode” to ON.
Hover over the table name in your Coda doc and click on the 3 dots that show up next to your table name. Click on “Copy table ID” and paste this value into YOUR_SOURCE_TABLE_ID.
To get your Google Sheets ID, get all the characters after /d/ in your Google Sheets file up until the slash and paste this into YOUR_GOOGLE_SHEETS_ID. See this link for more info.
Write in the name of the worksheet from your Google Sheets file where data will be synced into in the YOUR_GOOGLE_SHEETS_WORKSHEET_NAME value.
In Google Sheets, create a new column name at the end of your column headers called something like Coda Source Row URL and make sure there is no data in that column below the header. Write that column name in YOUR_SOURCE_ROW_URL_COLUMN_NAME.
Go back to Google Apps Script, click on the Select function dropdown in the toolbar, and select runSync. Then click the play ▶️ button to the left of the bug 🐞 button. This should copy over all the data from your Coda doc to Google Sheets.
To get the script to run every minute, hour, or day, click on the clock 🕒 button to the left of the ▶️ button to create a time-driven trigger.
Click Add Trigger, make sure runSync is set as the function to run, “Select event source” should be Time-driven, and play around with the type of time based trigger that fits your needs. I like to set the “Failure notification settings” to Notify me immediately so I know when my script fails to run.

Setup: Google Sheets to Coda script

Most of the steps above apply to the sheets_to_coda.js script as well but there are few extra features.

You can follow steps 1–10 above to fill out line 12 to line 18 in the script (except line 14 mentioned in the next step). The main difference is that “SOURCE” and “TARGET” are flipped around since you are now syncing from a source Google Sheet to a target Coda doc.
Your Coda table cannot have a column named Coda Row ID. If you need to use a column with this name, replace the TARGET_ROW_ID_COLUMN variable with another value.
If you have edit access to the Google Sheet, follow step 11 above and write in the column name in YOUR_SOURCE_ROW_URL_COLUMN_NAME.
If you want the ability to add rows to your Coda table and NOT have these rows deleted every time the sync runs, create a column in your Coda table and name it Do not delete. This column should be a checkbox column format and you will check the box for every row you manually add to your Coda table that you want to keep in that table. Otherwise, the script will delete that row and always keep the Coda table a direct copy of what’s in your Google Sheets file. If you change the name of this Do not delete column, you must edit the value of the DO_NOT_DELETE_COLUMN variable in line 22 of the script as well.
If you want the script to completely delete and re-write the rows in your Coda table each time the script runs, set the REWRITE_CODA_TABLE to true in line 23. This may make the script run faster, but may not be faster for larger tables (few thousand rows). For Google Sheets files where you only have view-only access, this setting will automatically get set to true.
Follow steps 12–14 above to set up your time-driven trigger.

Use cases with Google Sheets

Some of the most common use cases for integrating your application with Google Sheets can be found in the G Suite Marketplace for Google Sheets. From a business perspective, being able to visualize your data in Google Sheets allows you to slice and dice your data in ways you cannot do in on platform like Salesforce, for instance (FYI there’s a Salesforce add-on for Google Sheets).

The opposite is true too. Your team or company’s data may be stored in a Google Sheet but the data just sits there without being “actionable.” Let’s say you have a bunch of customer information and you want to create mailing labels with your customers’ names and addresses. Being able to “export” your data from Google Sheets into a mail merge application like Avery will make it easy to create the mailing labels you need.

Then there’s the pinnacle of productivity in Google Sheets: keeping data synced between your application and Google Sheets at all times.

When Google Sheets first came out, it was a game-changer since changes you make on your browser are instantly reflected in your colleague’s file. We have come to expect this with tools we use in the browser. But having data synced between Google Sheets and your other applications at all times is less common, and this is why the Google Sheets API is so important. From a Coda perspective, there are several use cases you might want to keep your Coda doc synced with a Google Sheet (and vice versa):

Data synced from your Google Sheet

HR & recruiting — All your candidates are stored in a Google Sheet but you want to be able to move candidates through different stages in the interviewing pipeline and Google Sheets isn’t sufficient for your needs. Having all your candidates in a table in Coda means you can use templates like this one to manage candidates more effectively.
E-commerce and ERP — Orders, customers, and POs may all be different tabs in a Google Sheet that gets updated through Shopify or some other e-commerce platform. In order to manage your e-commerce business, you may want to see charts, calendar of shipments, and reports that Google Sheets cannot provide easily. Syncing the data from Google Sheets to Coda means you can do ERP properly (see this template as an example).
Customer Feedback — You may have a ticketing system like Zendesk or Intercom and all feedback lands in a Google Sheet somewhere. You can do some basic analytics in the Google Sheet but to reply to the feedback means you have to go into Gmail and start replying to customers. If your customer feedback is all in a Coda doc, you can run analytics and send emails using the Gmail Pack (see this template).

Data synced to your Google Sheet

3rd-party vendor reporting — Your vendors may not be using Coda yet, but you have all your vendor data in Coda and need to send them the data in a format they prefer. While you could publish your Coda doc, the vendor still wants the data in a Google Sheet you have edit access to.
Data “backup” — Your team may create thousands of rows of data every quarter in a Coda doc and want to start each quarter “fresh.” Coda docs grow with your teams and they may get slow as you add in more functionality, so having a backup of your data in Google Sheets is another reason to sync data from your Coda doc to Google Sheets.
Finance & Accounting — Most internal finance and accounting functions still use Excel and spreadsheets for month-end reporting, taxes, and other business-critical activities. As your data grows in Coda, you can keep your finance counterparts in the loop by having your data synced to a Google Sheet which your finance team can use for their reporting and forecasting purposes.

Setting up Google Apps Scripts

Before you start using the scripts to sync data from Coda to Google Sheets or vice versa, you need to have Google Apps Script setup correctly. Just navigate to script.google.com and click on New Project. You’ll land in the GAS script editor. At this point, click on Resources→Libraries in the toolbar and you’ll want to paste in the following Coda library for Google Apps Script:

15IQuWOk8MqT50FDWomh57UqWGH23gjsWVWYFms3ton6L-UHmefYHS9Vl

After you add the library, you can pick a version of the library to use (I just picked the latest version to take advantage of all the latest features in Coda’s API):

Add Coda’s library for Google Apps Script

Syncing a Coda doc to Google Sheets

Setting up the script for syncing a table from a Coda doc to a Google Sheets requires a few simple inputs. I walk through how to get some of these inputs in my previous tutorial, so read that if you have any questions on how to get the following inputs:

Coda doc ID: This is the string of characters after the _d in the URL of your Coda doc
Coda table ID: The unique ID for the table you want to sync from in Coda. If you have Enable Developer Mode turned on in your account settings, you can get the table ID by simply clicking the 3 dots next to your table:

Google Sheet ID — This is the string of characters after the /d in the URL of your Google Sheet (see documentation here on how to get this ID).
Google Sheet worksheet name — Name of the individual worksheet in your Google Sheet you want to sync data into from your Coda doc
Source Row Column — This is the only customization you’ll have to do to your Google Sheet. You’ll need to add a column (typically the last column in your Google Sheet) that’s called something like Coda Source Row URL. This is the name used in the script. This is an important column to have in your Google Sheet since it will store the unique URL to a row in your Coda table. More about this later.

Once you have these inputs, you’re ready to get started with syncing your data!

Column names in Google Sheets

Try to keep the name of the columns in your Google Sheet the same as the columns in your Coda table. All the columns you want to sync from your Coda table to the Google Sheet should have its own column.

The one exception is the TARGET_SHEET_SOURCE_ROW_COLUMN variable which you’ll see in the script. Whatever value you put in this variable should also be the name of the column in your Google Sheet. You should put this column at the end of your table in Google Sheets like so:

Source row column to put in your Google Sheet

This column will be overwritten by the Google Script with the unique source row URL from Coda (every row in a Coda table has a unique identifier). The reason why we need this column for the source row URL is so that the script knows which rows have been added to the Google Sheet so that if you delete any rows in the source Coda doc, those rows can be deleted in the target Google Sheet. This brings me to a quick aside about the benefits of these source row URLs (these are called browserLinks in the API).

A unique row identifier

If you are a heavy user of Google Sheets, you may find yourself creating a “unique ID” column in table so that when you reference that row somewhere else in your Google Sheet, you can do a VLOOKUP to pull all the data related to that row. Sometimes you can get away with a column of data (maybe it’s a customer name, task name, or project name). For instance, in this screenshot the unique ID is the StaffID column:

Unique ID column in Google Sheets

To cover the cases where your table does not have a unique ID, the script puts the unique row URL from Coda into the TARGET_SHEET_SOURCE_ROW_COLUMN to act as the unique identifier. The Google Sheets -> Coda script also utilizes this column (assuming you have edit access to the Google Sheet). In lieu of this unique ID column, there’s no way for the script to know which rows have been added to the Google Sheet from Coda since there’s no native row ID system in Google Sheets (see this thread).

Fabricating a unique ID in Google Sheets

One alternative to fabricate this unique identifier in your data set is to concatenate a bunch of columns together in hopes that this new column will be the unique ID for that row:

Creating your own unique ID in Google Sheets

In the above screenshot, Feature is actually a pretty unique column of data. But to be 100% sure, there’s a Fabricated ID column which concatenates Feature, Team, and Milestone to create a “more unique” ID in the event there are two Features with the same name. This is not a perfect method due to two reasons:

The fabricated ID column might not be unique enough and it might be duplicated in other rows (which means you would have to concatenate more columns of data to fabricate that unique ID)
The columns you have concatenated may change (in this case, the Team or Milestone may change which would ruin the uniqueness of the ID)

In a previous life as a financial analyst, I employed this fabricated ID trick quite often but I had to choose the columns wisely. Typically in a report that has a time series, this would involve picking a dimension (e.g. west region), metric (e.g. sales), and the date for that specific row. This worked for static reports where data wasn’t getting deleted or updated too often. It’s a lot more risky to utilize this strategy with a shared Google Sheet with your team where data is constantly changing. Choose your columns wisely if you go down this path.

Mixing columns in Coda

The advantages of having a unique identifier for the rows in Coda also applies to columns in Coda as well (this benefit is realized in the Google Sheets -> Coda script). For syncing Coda to Google Sheets, the script has to use the actual names of the columns in Google Sheets since there’s also no native column ID in Google Sheets. This means if your column in Coda is named Projects but you accidentally misspell the column name in Google Sheets to Project, the data will not sync over correctly from Coda to Google Sheets.

One feature of the script is that you can re-order the columns in Coda and the data will still sync over correctly based on the column names. So your tables in Coda and Google Sheets could be organized like this, and the sync would still work:

The sortCodaTableCols() function re-arranges the columns in Coda to reflect the order of the columns in Google Sheets by simply looking for the column name in Coda:

var headerCodaTable = sourceRows[0]['cells'].map(function(row) { return row['column'] });
  var sheetsColOrder = [];

  headerRow.map(function(col) {
    sheetsColOrder.push(headerCodaTable.indexOf(col))
  })

  var sortedSourceRows = sourceRows.map(function(row) {
    var cells = sheetsColOrder.map(function(col) {
      if (col == -1) {
        return {        
          column: null,
          value: null,
        }
      } 
      else {
        return {
          column: headerCodaTable[col], 
          value: row['cells'][col]['value'],
        }       
      }
    });
    return {cells: cells}
  })
  return sortedSourceRows;

This means you can have your own “custom” columns in Coda or Google Sheets which can even contain formulas, and they won’t corrupt the sync from Task, Team, and Project to their respective columns in Google Sheets. As long as these custom column names in Coda or Google Sheets don’t show up in the other platform, then you can do whatever you want with these custom columns:

This could be useful if you work with a vendor who needs to see data in a Google Sheet to perform certain calculations that could be meaningful to them but don’t really matter to you and your Coda doc. As long as there isn’t a column name in the Google Sheet that matches the name of a column in your Coda table, then everything will work as intended.

Adding and deleting rows

The main runSync() function runs two other functions: addDeleteToSheets() and updateSheet(). The logic here is to add any new rows from Coda to Google Sheets and delete any rows from Google Sheets that were deleted from Coda. As mentioned above, the script uses a TARGET_SHEET_SOURCE_ROW_COLUMN to keep track of all the unique rows that need to be synced from Coda to Google Sheets.

An added benefit of using this “source row column” in Google Sheets is that you can add new rows of data to Google Sheets manually and leave the “source row column” blank. When the sync runs, the script essentially skips these new rows because they don’t have a URL that maps to an existing row in Coda. I’m not sure about the exact use case for when you would want to do this, but perhaps your Coda doc keeps track of sales from a store and your accounting team gets the data synced to a Google Sheet like this:

The columns in yellow are the ones that get synced from your Coda doc. The first 3 rows get synced correctly because you see values in the Source Row URL column. The accounting team realizes that there are more sales that were not accounted for and don’t exist in your Coda doc. They might manually add rows 5 and 6 and have a column they use internally called Manual Enter to keep track of the rows they are manually adding to the Google Sheet. When the sync runs next, rows 5 and 6 won’t get overwritten or deleted because they left the Source Row URL column blank.

Updating rows

The addDeleteToSheets() function was relatively simple to write, but updateSheet() was much more difficult given that rows in Google Sheets might be sorted in all kinds of ways. Additionally, I felt that scanning the entire Google Sheet for a source row URL and then scanning each column value to see if an update is needed was inefficient. Even if you have only 100 rows in your Coda doc that you want to sync to Google Sheets, that means there could potentially be 10,000 comparisons just for the row URLs alone every time the sync runs.

One option I considered was just blowing up the entire list of data in Google Sheets first (deleting all the rows) and re-writing the data from Coda to Google Sheets. This also didn’t feel right because for larger tables this could potentially hit Google Apps Script rate limits and would prevent the need for the addDeleteToSheets() function, prevent the need for the “source row column” in Google Sheets, and wouldn’t allow the user to manually add rows to the Google Sheet because those rows would get wiped out on the sync.

My thinking was to create two 2-D tables that were sorted exactly the same. The first table contains the rows from Coda that also exist in Google Sheets. The second table contains the rows in Google Sheets. The tables would contain the same number of rows and columns so you could then do a sequential comparison between the source Coda table and the target Google Sheet and see if there are any updates that need to be made in the Google Sheet.

The first thing to do was to convert the row objects in Coda to a 2-D table that is more similar to Google Sheets’ row objects. The convertValues() function “flattens” the Coda row object so that each row object simply contains an array of column values:

Most of the work in these scripts is actually just data munging so that the data is in format that is acceptable for Coda and Google Sheets. Once the tables are sorted in the same order in terms of rows and columns, the script can now check cell by cell if there are any chances that need to be synced over to Google Sheets.

I felt this sequential comparison of cells between the Coda and Google Sheets table was more performant than scanning for each row URL. The number of comparisons between the source and target tables is limited to the number of “cells” in either table. In this example, the script would only have to make 15 comparisons before figuring out that there are three cells in Coda that have been updated and need to be synced over to Google Sheets:

While this may seem like a performance boost, there is a lot of pre-processing to get the rows sorted correctly, so the net result might be same in terms of rows and cells scanned. There much more smarter people out there who understand sorting algorithms, so there may be an even more efficient approach here 🤷‍♂️.

A little helper sort function

In order to get the tables sorted perfectly before doing the cell by cell comparison, I needed to figure out a way to sort an array of arrays by some value. In this case, we have a bunch of arrays of column values that represent our rows, and the unique ID we want to sort on is the source row URL:

How do we sort each row object by the 7th element (row URL)?

I created this little sortArray() function that’s one of the workhorses in the script. It seems like such a common problem and I was surprised there wasn’t a built in sort function to sort an array of arrays (or maybe I just didn’t search hard enough). So if I want to sort the targetRows object below which contains all the rows in my Google Sheet, I run the sort() function on it and pass in the sortArray() function and the returned sortedTargetRows object is…as you expected…sorted by the source row URL:

var sortedTargetRows = targetRows.**sort(sortArray)**;

function sortArray(a, b) {
  var x = a[rowURLIndex];
  var y = b[rowURLIndex];
  if (x === y) {
    return 0;
  }
  else {
    return (x < y) ? -1 : 1;
  }
}

One thing I learned about the sort() function is that if you pass in what they call a compareFunction (in my case the sortArray() function), to sort values by alphabetical order, it actually sorts in alphabetical order for values with uppercase letters followed by lowercase letters. Here is a list of values and how you expect them to be sorted versus how the sort() function actually sorts stuff:

WTF?

Now if you sort this list of values in a spreadsheet or Coda table, you’ll get the results in the What you expect column. I couldn’t figure out why the sorted values didn’t match up with what I expected after sorting the values in Google Sheets. Then after some debugging I realized this is the default behavior of the sort() function in JavaScript. A common workaround is to apply the toUpperCase() function to the value so that you are doing a case-insensitive sort. Unfortunately, this won’t work for the script because it’s possible for a table in Coda to have two row IDs with the same order of six characters but just be capitalized differently (e.g. a row ID of NPmgrG and NPMGRG could exist in the same table).

In our case, we need to find a case-sensitive sort to account for the uniqueness of row IDs. I searched for a function like this to no avail. Then I realized it doesn’t matter if the script doesn’t sort the table in the alphabetical order I expect as long as it applies the same “incorrect” sort to both the source and target tables equally. This means both tables will still be sorted in the same order just not in the order we expect from a typical sort in Google Sheets or Excel.

Syncing Google Sheets to a Coda doc

After writing the Coda -> Google Sheets script, I thought the Google Sheets -> Coda script would be a breeze since I had written all the functions to convert and sort data. All I would have to do is just switch around some variables and everything would work out just fine. Turns out I was completely wrong since there are a bunch of edge cases to account for in Google Sheets that makes the sync a little more difficult compared to Coda to Google Sheets.

You can follow most of the steps in the Coda to Google Sheets setup to get the values you need for the script to run, but there are a few caveats and extra options you can set to get similar functionality as the Coda -> Google Sheets script:

Target Row Id Column — This is a key that stores each row’s unique ID from a Coda table. This value is in the “source row URL” (last 6 characters). Be default this variable is set as “Coda Row ID,” so make sure you don’t have a column in your Coda table with this name.
Do Not Delete Column — Unlike Google Sheets, the script is not written in a way where you can add additional rows to the target Coda table without having them deleted when the sync runs. As mentioned above for the Coda -> Google Sheets script, you can add rows to your target Google Sheet and not have them deleted on the sync. You need to create a checkbox column in your Coda table called Do not delete and check off the box for that row if you don’t want it to get deleted on the sync. If you prefer a different column name, just change the value for the DO_NOT_DELETE_COLUMN variable.
Rewrite Coda Table — Unlike the Coda -> Google Sheets script, you have the option to completely delete all the rows in your target table and re-write them with all the rows from your source Google Sheet. Set the REWRITE_CODA_TABLE variable to true if you want this behavior (may result in a faster sync).

Column and row limitations

If you have edit access to the Google Sheet, you will need to add a column at the end of your table called something like “Source Row URL” similar to the “Coda Source Row URL” pattern mentioned above for the Coda -> Google Sheets script. After writing the data from Google Sheets to Coda for the first time, the unique row URLs from Coda are copied over into this “Source Row URL” column in your editable Google Sheets. Obviously this doesn’t apply to Google Sheets where you only have read-only access (more on that later).

One limitation of the script is that if you add a new column to the Google Sheet, you also need to add that same column name to the Coda table. It’s ok if the column order isn’t the same in Coda, but that column name just needs to exist somewhere in the Coda table. You can just hide the column in Coda to make the table nice and clean. This is actually a limitation caused by the way I structured the script, so hopefully it doesn’t cause you too much inconvenience 😬.

Be careful with empty rows in your data in Google Sheets because those rows also get “synced” over to Coda. Not only will those empty rows show up in your Coda table, they will get their own source URLs. Ideally, the Google Sheet won’t contain any empty rows and this won’t be a problem for you.

A few other “small” things:

Formulas don’t sync — Probably not a huge surprise as this is also a limitation of the Coda -> Google Sheets script. Any columns with formulas you sync over to Coda will just be hard-coded to that column in your Coda table.
Resetting column formats — When your Coda table is blank and you’re syncing over rows for the first time from Google Sheets, you may have to change some of the column formats to the proper format. For instance, if your dates in Google Sheets are in Zulu format, Coda will sometimes interpret these values as a select list. After the sync, just change the column format in Coda to the Date format you want and future syncs will work just fine.
You can’t sort your Google Sheet — The script looks for empty source row URLs in the SOURCE_SHEET_SOURCE_ROW_COLUMN in your Google Sheet and it scans that column until it finds an empty value to start pasting in new source row URLs from Coda. If you sort your table, that column will get all jumbled and the script will break. New rows that you add to the Google Sheet should have the source row URL column blank and these blank cells need to be contiguous.

Setting a timer for source row URLs

You will notice that the data syncs over pretty quickly to Coda, but the SOURCE_SHEET_SOURCE_ROW_COLUMN (aka the “source row URL”) takes a couple seconds to show up in your Google Sheet. The reason this happens is because of the steps that need to happen for this sync to work:

Find the rows that need to be added from Google Sheets to Coda
Insert those new rows into Coda
Coda snapshots the new data added to your table
Look to see if the source row URLs have shown up in the Coda table
Copy over the source row URLs to Google Sheets once those URLs show up

The key step in #3 since that snapshot can take a few seconds to happen. If we try to copy the source row URLs right after the rows are inserted into the Coda table, the script will come up with nothing an no row URLs will show up in your Google Sheet.

To get around this, I added a little sleep timer to basically check for the source row URLs every two seconds:

while(currentCodaRows.length <= allRows['targetRows'].length) {
  timer += 2;
  if (timer == 60) { break; }
  Utilities.sleep(2000);
  currentCodaRows = retrieveRows();    
}

The allRows[‘targetRows’] object contains all the rows in your Coda table when the script runs for the first time. Every two seconds, the loop retrieves the rows in the Coda table in hopes that the the number of currentCodaRows has exceeded the number of original rows when the script first ran. The loop also breaks after 30 seconds if, for some reason, the Coda API cannot retrieve all the number of current rows added to the table. So far it hasn’t taken more than five seconds for the URLs to show up, but this is on a small data set of a 5–10 rows being added each time I tested the script.

This sure seems like a heck a lot of work just to added some new rows to a table in Coda. That’s why I put in a REWRITE_CODA_TABLE variable to override all this source row URLs business.

Deleting and re-writing rows each time

As discussed with updating rows in the Coda -> Google Sheets script, I wanted to avoid this pattern of syncing data:

Delete all rows in the target
Copy all the rows from the source
Insert the copied rows into the blank target table

It didn’t seem like the right solution especially for a large table of thousands of rows because if you’re only changing or adding a few rows, the script has to delete and re-add all these thousands of rows. The simplicity of this approach is tempting, nonetheless. Just like the Coda -> Google Sheets script, the sheGoogle Sheets -> Coda script is broken down into addDeleteToCoda() and updateCoda() functions. The former function adds and deletes rows while the latter updates any existing rows in Coda that may have changed in the source Google Sheet.

Blowing up the Coda table each time the sync runs would prevent the need for individual functions that add, delete, and update because the nature of blowing something up is that you can re-build from scratch. I haven’t measured which option is more performant but my hunch is that for smaller tables of data, setting REWRITE_CODA_TABLE to true may actually make the script run faster at the expense of not having the source URLs in your Google Sheet.

The REWRITE_CODA_TABLE option is actually important for Google Sheets files you only have read-only access to. By default, you can’t write source row URLs to a Google Sheet you have view-access to, so there’s no point in using source row URLs to figure out which rows need to be added, deleted, and updated. Side note: the script doesn’t work on Google Sheets that have been published to the web. You’ll know the Google Sheet is published when the URL has a 2PACX in the URL like so:

Getting permissions from Google Sheets

Instead of having to remember if you need to switch the REWRITE_CODA_TABLE variable to true when you’re syncing from a read-only Google Sheet, I did a little hack to get the permissions you have on the Google Sheet by trying to add the logged in user (you) as an editor to the Google Sheet:

function sheetsPermissions() {
  try {
    fromSpreadsheet.addEditor(Session.getActiveUser());
  } 
  catch (e) {
    REWRITE_CODA_TABLE = true; // If no access automatically rewrite Coda tables each sync
  }
}

If you have edit-access to the Google Sheet, nothing happens since you are already an editor. If there is an error, then that means you don’t have permissions to add yourself as an editor to the Google Sheet (which means you only have read-only access). Int this case, the REWRITE_CODA_TABLE is set to true and the script goes on and blows up the Coda table and replaces it brand new with data from your Google Sheet.

Final Caveats & Notes

There are many other variables to consider before implementing these scripts into your daily business-critical processes, but I think the given feature set should get you 90% of the way there. Having said that, there are a few more things to think about and small limitations about the scripts in general I’ve discovered along the way. This is by no means an exhaustive list.

Using simple triggers in Google Apps Script

I thought that the Google Sheets -> Coda script could take advantage of simple triggers to fire off the script. Basically you could have the script fire right when you make an edit to any cell, the moment the Google Sheet loads, etc. Unfortunately, there are a few restrictions to using simple triggers, and it looks like the script has to be entirely contained in Google Sheets to utilize simple triggers. Additionally, I don’t think the script could keep up with the speed of edits if you are looking for near real-time syncing. Data would just get choked as the script waits for source row URLs to appear and data would start pouring into your Coda doc.

Rate limits

There are rate limits for Google Apps Script as well as Coda. I’ve tried syncing tables with 10,000 rows in both scripts (6 columns) and they both seem to work. I think in one test the Google Sheets -> Coda sync resulted in some rows missing in the Coda table. For the first time you sync data over, I’d recommend just doing a regular copy and paste instead of relying on the sync to copy all the data over correctly. Most likely, subsequent additions and edits would be as large so the sync should run smoothly.

V8 runtime

If you have existing Google App Scripts, you may have noticed this fun error message at the top of your editor:

These scripts utilize the V8 runtime which takes advantage of a bunch of modern JavaScript features. The only changes I needed to make to upgrade the scripts was changing the syntax for for each loops.

Moving off Coda or Google Sheets to a dedicated database

It’s tempting to use a Google Sheet or Coda doc as your de facto database. The interface is familiar, easy to edit and use, and it lives in your browser. The danger is when it feels so convenient that you start putting thousands or hundreds of thousands of rows into your spreadsheet and maybe rely on Zapier or these Google Apps Scripts to sync data in and out of other applications you use every day to get work done.

If the process isn’t business-critical and your team can put up with this annoying little thing:

Source: Ben Collins

…then by all means continue doing what you’re doing and pass the Google Sheet to the next intern or analyst who has to put up with updating it in the future. I would consider migrating your data to a dedicated database platform (like Google BigQuery) which has a nice integration with Google Sheets. Lots more to say about this subject, but I’ll just leave it at that.

Not a programmer

Most of this post is me pretending to know what I’m talking about. I’m not a programmer, and the scripts could probably be improved 10X by someone who actually knows what they’re doing and understands how algorithms work. There are unnecessary loops and bugs stamped all over the scripts so please proceed with caution ⛔️. If you happen to be someone who knows more about this stuff than me, consider contributing to the code. I just did the bare minimum to get something to work and hopefully these scripts will be sufficient to get you on you your merry way of not having to copy and paste between Coda and Google Sheets 🤙.

Dear Analyst #29: Working with dynamic array functions and formulas that spill

Al Chen — Mon, 13 Apr 2020 14:27:10 +0000

Have you ever wondered what an “array-entered formula” is? It’s an intermediate/advanced concept in Excel but in late 2018, Microsoft released dynamic array functions and formulas that “spill” into the cells below your current cell with a function. This makes writing formulas easier and less prone to human error, but there are some tradeoffs to using these formulas which I discuss in this episode.

Source: Microsoft

Implicit intersection: what Excel does behind the scenes without you knowing

This is not meant to cause fear as in Excel is doing something “behind your back.” Many Excel users don’t know that Excel does some magic behind the scenes for formulas where the input may be a range of cells but the formula is not necessarily a formula that is meant to accept a range of cells. Excel does something called Implicit Intersection.

Source: Exceljet

With dynamic array functions turned on in your workbook, you may have to start using the “@” operator to tell Excel to keep implicit intersection “on.” There are a lot of edge cases where you would need to use the “@” operator so I’d recommend reading this blog post if you would like to learn more.

Bringing array formulas to the masses

I argue that dynamic array functions and spill formulas are giving new Excel users a way to quickly calculate, filter, and sort their data sets without needing to go through a myriad of menus in the toolbar. Given that more jobs these days require working with large data sets and familiarity with various data models (SQL, NoSQL, GraphQL), knowing how to quickly manipulate data that’s structured in one of these database models is becoming more important than ever.

I think that advanced Excel and SQL users will notice that Excel is getting closer to how PivotTables and SQL operate. With PivotTables, you have calculated fields which are similar to dynamic array functions in that you write the formula once and it applies to your entire PivotTable no matter how you slice and dice your data. In SQL, you are pretty much writing your own user-defined fields and aggregating data from other columns.

Other Podcasts & Blog Posts

In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

SheetsCon: Not a podcast, but a great virtual conference all about Google Sheets and all the replays are free to watch
devMode.fm #66: Interviewing for a Webdev Job in 2020

On mastering your tools

Al Chen — Wed, 11 Mar 2020 17:24:59 +0000

For some of us, work is moving bits and pixels from one side of your computer screen to another. Moving your mouse and plugging in your laptop might be the most physical task you do during the workday. No matter how you define work, you are participating in skilled labor — you have developed an asset that others find valuable.

When I think of traditional examples of skilled labor, the blacksmith comes to mind — a man heating iron in a forge, pounding on iron with a hammer, and toiling away in his shop. Something like this:

Source: Improvement Era, 1941
Source: Joe Sanna

Historically, blacksmiths held an important position in the village. People might go to them for a pedestrian project like fixing a small appliance. They might also get called upon to fix wagon wheels, create armor, and even weapons to be used by the military. A blacksmith’s knowledge of heating iron with carbon gave them the ability to turn a natural resource into any tool that was needed in the village. Naturally, the definition of the job could be extended to bladesmith, nailmaker, and even dentist.

Codans as blacksmiths: We stepped away from building Coda docs one night and forged steel objects at Lawless Forge in Seattle.

Whatever project was thrown at the blacksmith, they found a way to produce the tool necessary and become masters of their craft — something I feel everyone should be empowered to do as they challenge the status quo of their own tools.

Once a toolmaker always a toolmaker

Think about all the online tools and applications you use at work today. Email, internal real-time chat, video conference, project tracking; all these platforms have an opinionated way of how you should get your work done. Have you ever asked yourself the question: why must we communicate or track projects the way the software dictates?

I started asking myself this question early on in my career as a financial analyst. Spreadsheets were a means to an end. I took some numbers, move them around the cells, and bam! The work was done. My stakeholders and management had the numbers they needed, and I could move forward with analyzing another data set.

As I wrote in this post, the skills I learned from analyzing data in spreadsheets prepared me for a career of toolmaking.

I started to question the process in which the work on my team gets done (which typically involved data in a spreadsheet). Instead of the spreadsheet being an opinionated tool for how I should analyze data, I had my own vision for how the spreadsheet could be molded and fine-tuned for my team to work better. The spreadsheet became my iron.

Source: Western Maine Blacksmith Association

Aside from wanting to help my team be more efficient and productive, I could never switch back to a state of accepting a spreadsheet with poorly structured data, incorrect formulas, and inconsistent formatting. Similar to a blacksmith refining his technique through trial and error, I discovered keyboard shortcuts would make me as a toolmaker more efficient in…well…making tools. The spreadsheet would be molded to fit the requirements of the project, and I would accept nothing less.

Makers as blacksmiths

As a master of their tools, the blacksmith knows all the shortcuts to bypass the inefficiencies with building or creating their next project. Makers share many similar characteristics as blacksmiths. Software engineers, for example, are always thinking of the most efficient method of solving a problem.

Clive Thompson discusses these characteristics at length in his book Coders: The Making of a New Tribe and the Remaking of the World. In this Wired article, Thompson tells the story of a software engineer named Jason Ho who was helping his dad with creating a more efficient employee clocking time system for his dad’s business. Ho created a prototype in 3 days and eventually productized the tool and started generating $10,000 per month from his project. Thompson describes the reason coders have a passion for efficiency:

Removing the friction from a system is an aesthetic joy; coders’ eyes blaze when they talk about making something run faster or how they eliminated some bothersome human effort from a process.

Thompson’s book includes stories about engineers who may not be optimizing for a financial outcome when working on various projects. For blacksmiths, I’d argue just being able to build the tool, knowing the tool works as intended, and helps others results in an “aesthetic joy.”

One of my favorite stories about a maker-turned-blacksmith who saw an inefficiency with how Instagram influencers get compensated built a bot that would post beautiful pictures of NYC on Instagram:

Chris Buetti wanted to get free lunches in NYC so he had his bot post eye-catching photos with captivating captions to get thousands of followers. He then automated the emails to restaurants saying that he would post about them on his Instagram account if they gave him a free meal. Buetti was a master of his tools, and used it to not only score free meals, but shed some commentary on the influencer ecosystem that has been generated by platforms such as Instagram.

You are a maker

My own maker experience is far from unique — Buetti’s own experience shows that the barrier to being a creator in today’s digital landscape is very low. The rise of “maker” communities ranges from YouTube creators on one end of the spectrum to engineers releasing cutting-edge open-source technologies on the other. This TED@MotorCity talk by Dale Dougherty of MAKE magazine gives a nice overview of the “maker movement,” which has evolved to include people building digital products.

Your perception of a maker might be someone who is good with building with their hands or likes tinkering with electronics, but I believe the definition of a maker stretches way beyond that. You may not identify as a maker today, but chances are you have already “made” something in the past month. For example, if you have created YouTube video, taken a recipe and changed it a bit to fit your tastes, or invented a new workout routine at the gym, I would argue you are a maker. There is a lot of creativity that goes into creating engaging online content, and understanding the algorithms behind platforms like YouTube require patience, testing, and experimentation.

For example, think about Cassey Ho, creator of Blogilates, the #1 female fitness channel on YouTube with over 500 million video views and 4 million subscribers.

As a fitness instructor, she deeply understood the barriers people face to exercise and decided to challenge the status quo of the traditional pilates class. By combining fun music and innovative choreography, Cassey created an entirely new genre of exercise: Pop Pilates. Through her dedication to her craft, she shaped the fitness industry and paved the way for YouTube creators to come.

Or consider one of my favorite TV shows growing up--MythBusters with Adam Savage and Jamie Hyneman. Adam and Jamie would spend the whole show creating some contraption to test a hypothesis and potentially dispel a myth. What I loved the most about the show was that these contraptions were sometimes way over the top and Adam and Jamie probably could’ve tested their hypothesis with a highly controlled experiment in a lab. As the duo are building their contraption, you could feel the excitement and joy during their process of imagining and building their creations even though the final test may only last a few seconds.

Source: Business Insider

The key takeaway from the show that applies to this story is that becoming a maker doesn’t mean you have to be driven by profit, fame, or views. What Dale and and MythBusters teaches us is that you can be a maker by just being curious about the world around you and the tools you use. During a 2012 Maker Faire talk, Adam sheds some light on his journey becoming a maker:

It doesn’t matter what you make. And it doesn’t matter why. The importance is that you are making something. When you are making, the process you’re going through, the problem solving, the shaping your future with your hands, it’s inherently a good and positive conversation. It makes you into a critical thinker.

Be the master of your craft

In Hammer’s Blow, one of the journals produced by The Artist-Blacksmith’s Association of North America, you’ll typically find tutorials and diagrams on how to build certain tools and crafts. The content is not too dissimilar from a video tutorial you might find today of some digital cloud platform. The author of one of the issues pays homage to and reminisces on a conversation with Francis Whitaker, a famous blacksmith who passed away in 1999:

Hammer’s Blow, 2000

You’re the blacksmith…you are the expert on ironwork. You must do the highest standard of work possible, then use that work to teach your clients of the possibilities of hand forged iron. Through the work you will find the clients who value it. — Francis Whitaker

By becoming a master of ironwork, Whitaker was able to create pieces that his clients didn’t even think were possible. He teaches us that knowing a tool inside and out not only makes you an expert on that tool, but gives you satisfaction in knowing you are getting closer to perfecting a craft.

I think the values of the Artist-Blacksmith’s Association can crossover to so many other industries. Especially if you are in an industry that is averse to change and generally doesn’t adopt new methods or ways of doing things. The Artist-Blacksmith’s Association shows us that we can all be makers and change the course of our careers and industries. This quote from the Artist-Blacksmith’s Association of North America’s mission statement is illuminating:

With hammer and anvil, we will forge for mankind a richer life. We will preserve a meaningful bond with the past. We will serve the needs of the present, and we will forge a bridge to the future. Function and creativity is our purpose. Our task is great and so is our joy.

While the traditional blacksmith only had an anvil, hammer, and iron at their disposal, today’s digital maker has hundreds of SaaS tools, design tools, and APIs to build unique tools that traditionally would take a large development team to build. Instead being a master of one “tool” like the spreadsheet, these makers are masters of integrating toolsーbeing able to connect disparate tools together to build something greater than the sum of its parts.

Like Whitaker said, we should all pursue the highest standard work possible, shining a spotlight on ideas others have never seen before. In order to do this, we can no longer be complacent with the tools we are given. As we acknowledge ourselves as makers, we should feel empowered to become masters of our crafts and challenge the status quo of the tools we use.

How Playing StarCraft Can Teach You About Startups and Life

Al Chen — Tue, 28 Jan 2020 03:38:48 +0000

This post originally appeared here.

It's rare to read an article that keeps me thinking about the article days and weeks later; even rarer for it to inspire me to write an article of my own. What was the subject of this earth-shattering article, you ask? None other than the RTS (real-time strategy) computer game StarCraft II.

Shopify CEO loves StarCraft

This article from Mike from his "Non-GAAP Thoughts" newsletter talks about the business lessons Tobi Lutke (CEO of Shopify) learned from playing StarCraft. Mike also provides his own analysis of Shopify's business strategy and how it resembles different strategies you'll encounter playing StarCraft. After reading this article, I of course Googled instances where Tobi publicly talks about StarCraft, and my favorite gem of a video is this one:

Tobi is probably the highest profile business person who talks publicly about StarCraft. In Mike's article, Mike compares Shopify's success with the creep feature of the Zerg race. Amazon may be the e-commerce giant, but Shopify is growing bottoms up by allowing anyone to "spawn" their own online store using Shopify as their backbone. The Zerg race, as Mike describes, is hard to defeat later in a game of StarCraft which foreshadows Shopify's future as an e-commerce technology player.

Others talking about StarCraft and business strategies

Tobi's love for StarCraft is made apparent on various podcasts, articles, and recently livestreams (as shown from the video above). Some business reporters have taken notice and pose some facetious questions about what this means for Shopify as a business:

📉tae kim

@firstadopter

CEO of $43 billion tech company is streaming videogames on Twitch rn. Not sure if this is a bullish or bearish signal. What do you think? $SHOP

18:47 PM - 04 Dec 2019

1 42

As a shoddy StarCraft player myself, I know enough about the game to understand why business and startup folks draw analogies from the game to their professional lives. A quick Google search for "StarCraft and business" will yield some very well-written articles discussing the game and business that would compete with a business strategy article you might find in Forbes.

The classic article about this topic is from Charlie Cheever, co-founder of Quora. He wrote an answer to the question: "What are some lessons learned through playing StarCraft that are useful in real life?" on Quora, of course. The answer is worth reading in its entirety, but here are the main takeaways:

There's no substitute for taking a lot of actions.
Macro is usually more important than micro but at critical moments, micromanagement can mean the difference between massive success and disastrous failure.
There is usually more than one way to accomplish something.
Timing is critical.
Execution is first order more important than strategy but there's also a ceiling on how well something can be executed, and then strategy matters.
Long term success is usually achieved by getting a small advantage and then using that to get some other kind of advantage.
Talent is a prerequisite for being top notch at most things, but in almost any field, no one gets really good at anything without putting in more focused effort and time than other people.
Different people (/units) are good at different things.
If you are better than your competition, you want the game to go on longer.
It's not one big thing but a bunch of little things that add up.

If you didn't know this post was about an RTS computer game, you might assume that these strategies are straight from the latest NY Times bestseller list of business books. The fact a diversity of lessons can be learned from StarCraft keeps me playing the game for more than 20+ years. Granted, I don't play as much as my younger days, I'm able to draw on more years of work experience to see the analogies between the game and my professional life.

Iterate or die

Perhaps no other lesson rings more true than this: constantly iterate. If you're working on a startup, iterating has probably been ingrained into your DNA. Courtland Allen, founder of the Indie Hackers community, talks about the fast iteration cycle in StarCraft in this Software Engineering Daily episode. You are constantly pushing forward and defending. Trying new strategies while sticking to the tried-and-true methods that you've learned from other more experienced players. The big difference between a startup and StarCraft is this:

One year of making decisions at a startup is compressed to 5 minutes of gameplay in StarCraft.

Lucky for us, you can experiment with new build orders, unique armies for offense, and counter attacks without the fear of losing real money, alienating your friends and family, and sleep. I take that back. Gaming addiction is real, but let's assume we're casual gamers just curious about how StarCraft can teach us about a word that has become so popular in startupland: failure.

You have to lose (a lot) to ever be a winner. As you get better the matchup will try to move you up the ladder to people who are your equal or better in ability. - Giovanni Dannato

This quote is from another blog post about life lessons learned from StarCraft, and is one of my favorite lessons I've gleaned from the game. You can lose over and over again, and each loss is another chance to learn about what you did or didn't do that contributed to the loss. You might have spent all this time building up your economy (gathering resources in the game to build other buildings and units), and didn't spend enough time building an army to attack your opponent. You lose the game, adjust, and come back stronger the next game.

It's easy to criticize when things don't go right in business or startups, and more difficult to celebrate the wins. The losses, however, give you the best chance to learn and practice so you can come back stronger on your next venture. In between losses though, changing your beliefs about the right build order and army composition is critical to getting better at the game. Perhaps Tobi thought about challenging Amazon 14 years ago, and didn't see much success. Instead, he iterated and built the plumbing for hundreds of thousands of little Amazons around the world to solidify Shopify's position in the e-commerce industry.

Trough of sorrow

Another perennial startup phrase coined by the infamous Paul Graham: Trough of Sorrow. Startup founders have probably seen and experienced versions of this cycle:

I see this cycle play out in most of my games because I'm not as good as my co-workers whom I play with. One of Charlie's lessons mentioned above resonates with me: If you are better than your competition, you want the game to go on longer. The games where I usually do the best are the ones that last 5 minutes or less and I "rush" my opponent. In StarCraft, rushing means you build the bare bones of what you need to launch an offensive attack, and take your modicum of an army and try to submit your opponent into tapping out. This strategy can backfire, of course, because your opponent may have enough units to defend your attack, and respond with a counter attack. If the game lasts longer than 5 minutes, that's where I experience the trough of sorrow where I lose small skirmishes here and there, and never build enough buildings and units to last late in the game.

When you launch your startup, there's a wave of euphoria that comes with the big press release or TechCrunch article. Maybe your competition doesn't have the resources or units to respond, and they fold quickly (or drag out the inevitable). While the following chart shows the fall of yellow cabs in NYC over a few years, Uber and Lyft clearly launched and never looked back:

Most startups never make it past the TechCrunch of Initiation phase and die off during the trough of sorrow. If I had to compare a long game of StarCraft to the startup cycle, here's how I would break it down:

Maintaining balance in a fast-paced environment

I think it's extremely tough to maintain a work-life balance when working for yourself or at a fast-paced startup. Responsibilities and projects change daily and your ability to react and work under pressure is tested.

You have to move quickly.

This is one of the lessons from this Business Insider article from the Young Entrepreneur Council. Taking the Startup Curve above as context, being able to move quickly in the Trough of Sorrow and Wiggles of False Hope in StarCraft is where I struggle the most.

As you are building out your economy and units, you may engage with small battles and skirmishes and not only do you have to move quickly (literally clicking your mouse and hitting keys on your keyboard), you have to make the right decisions in the heat of the moment or else you can quickly lose your army and the game. During these moments, I will forget to A-click, siege my tanks at the wrong time, or click around maniacally on the game map to respond to an oncoming attack. These momentsーaka "micro" in StarCraft"ーdefine the outcome of the game and it's something I want to get better at.

Back to the Trough of Sorrow and Charlie's lesson about wanting games to go longer if you are better than the competition, the longer games are difficult because during those 5-10 minutes of "sorrow," I constantly ask myself: "what should I be doing right now?" The right answer is build more units and gather more resources. But what do I do with those units? Am I building the right number and diversity of units? Should I send them to a certain part of the map? Should I talk to my teammates and see what they want to do?

As you're working through the Trough of Sorrow on your startup, is working on SEO the right strategy? Which partnerships should you focus on? Do you start fundraising? These are all questions you ask yourself and the common answer many founders will say is "we're going to do everything." In my experience, leaning into one strategyーlet's say SEOーand working on writing good quality content, getting backlinks, and owning your niche online is the best way to go. Along the way, you'll respond to new competitors and entrants, but you are focused on learning along the way to improve that one strategy.

This quote from one of my favorite YouTube channels about StarCraft says it best:

I'm going to talk more generally on how to learn. How do you get better? The most important thing is not about winning or losing; but about improving. The only person you should be beating repeatedly is yourself. - Winter

In startup world, you don't have the luxury of "losing" just to improve. You can run out of cash, stop fixing bugs, and there is no next game. If you have a bit of runway, you can focus on improving and try various strategies for growth until you find the one that works for your business.

StarCraft and life

This is where the story can get a bit fluffy and confirmation bias sets in.

I sent Mike's article to a friend of mine who also plays StarCraft, and he made a comment about Tobi playing StarCraft worth reflecting on. He said something along the lines of:

I would love to be a billionaire one day and be able to say that the one thing that contributed to my success was StarCraft.

If Tobi wasn't a billionaire and CEO of an amazing company, would people be as enamored with his stories about StarCraft? If he were to draw parallels between knitting, or jiu jitsu, or some other "hobby" with his success as a CEO, would we be convinced by the argument? Maybe.

For StarCraft fans who play casually, there's a mystique about the game that makes it easy to bridge the lessons from the game to real life. My favorite story about one casual StarCraft gamer's foray into the StarCraft community is this article by Paul Miller. His sentiment about watching games of StarCraft is spot on:

When two top players clash, something beautiful happens. There are little impossible moments, where the physical limitations of the computer interface fall away, and armies appear to be an extension of each player's mind. The battles’ tidal ebb and flow and scrappy tug-of-war over every inch of the map make a pixel vs. pixel war between aliens and humans suddenly seem like the most natural, vital thing in the world.

After you read that article, it's difficult not be moved by the community enthusiasm behind the game. Maybe for some, it's just a game. For others, it's so much more:

From the community

If you've made it this far, my guess is you play StarCraft, or wanted to find a way to procrastinate at work today and why not read about how a computer game might be able to teach me about running a business? Either way, this Quora thread is full of gems on how the game has caused people to re-think and examine their lives and careers. The game definitely has for me.

Some final quotes:

The losses that taught you something, is more valuable than the wins you learned nothing from. I know it sounds like some cheesy motivational quote from a bad Facebook post, but Starcraft honestly influenced the way I deal with small defeats in life. - teefax

I've learnt that you can always 'macro' out of situations and it's never over. I kind of think about life as one big long macro game and if I fuck up it's ok cause I know I can always get myself back in the game. - ZephyrBluu

While my peers have a special NES-honed tolerance for frustration, my typical game cycle involves obsessing over a title, reading the reviews, buying a copy, playing for a few hours, and never touching the game again. StarCraft II broke that cycle, but it also threatened to break me. - Paul Miller

Bonus

Once in a while, my colleagues and I have our StarCraft games cast by a professional StarCraft caster (check out his YouTube channel). Got permission from my team to post this replay and would appreciate any critiques on my gameplay (my handle is bertman).

5 Ways to Succeed As A New Remote Worker

Al Chen — Mon, 13 Jan 2020 20:48:39 +0000

This post originally appeared on the Remote-how blog.

Prior to joining Coda, I had never worked remotely for a company.

In my previous role, I would work from home every now and then, but for the most part I was expected to show up to the office, attend meetings I didn’t really want to attend, and then get work done at my desk. I had the opportunity to freelance and work on my own projects, and with that came the responsibility of setting my own hours and getting work done. For most independent consultants and freelancers out there, you are working remotely but don’t have a bigger team to answer to (unless you are working on-site for a specific client).

Being a freelancer taught me a lot about routines, discipline, and knowing how to structure my days, weeks, and months. It didn’t teach me how to work with a broader team that is collocated in a different city. I want to share 5 tips on how to be successful when joining a company as a remote worker for the first time in your career.

1) Visit the team in person often

Nothing beats meeting your team in person and building real relationships. When I first started, I tried to visit our offices on the west coast as often as I could (I’m based in NYC). My first day was actually at an internal hackathon where the entire team gets together and works on brand new ideas that may eventually get built into the Coda platform. During the hackathon, I had a chance to meet almost everyone on the team in a somewhat stressful environment (true to the spirit of hackathons, we work on ideas for 2-3 days straight). During those few days, I got a chance to meet and work with my new teammates so that they could understand my working style.

Every month or so, I would fly back for company events or just to work alongside my team. When you’re a new remote worker and don’t make an effort to meet your team in person, your teammates will only know you through Slack and on video conference calls. Taking the time to build relationships with your team members up will engender more trust and productivity down the road.

2) Be vocal through documentation

All remote workers don’t have the opportunity to have “water cooler” conversations that their collocated colleagues have. These informal conversations can create clarity for a business issue and creates more collaboration between team members. Since you can’t regularly have these hallway conversations, you have to be a little more vocal through digital means. This means e-mail, Slack, and for me, Coda.

Aside from leaving feedback via Slack and e-mail, I use Coda heavily to upvote/downvote questions I have about projects, document the status of projects, and more. The various company-wide Coda docs we have set up allows for transparent sharing of company information and gives anyone in the company to voice their opinions and feedback. I use our various Coda docs in real time during meetings so that the meeting leader can see any questions or feedback I’m typing in as the meeting progresses.

3) Get a high quality webcam and mic

This is a logistical aspect of being a new remote worker but many people decide to put convenience over quality. It’s easy to the headphones you already use with your iPhone or Android phone into your laptop and start getting on Zoom calls. On the other side where your entire team is watching and listening, the quality can come out really bad. You don’t want to be the one person on all Zoom calls where the audio and video quality is bad and people just want you to mute your mic.

One of my favorite podcasters is Jason Calacanis and he has some very strict rules for his team on how to handle video conferences. If someone on his team is dialing in remotely, he requires them to have a headset with a stick mic, a webcam that gets plugged into their computer, and have a wired Ethernet connection. All these precautions help ensure good video and audio quality. You can get $20 headsets on Amazon that have amazing audio quality. I’ve been using this one for the last 2 years and it works perfectly.

4) Join IRL communities

Related to tip #2, you won’t have the opportunity for in-person lunches or happy hours that often with your team. Instead, I’ve opted to join in-person communities in my city to get that social interaction that comes with being a part of a team. For instance, at my co-working space (WeWork), I started going to events and meeting people that work near me to have some “work friends” even though they are not a part of my company. I created a meetup for Coda users in NYC and it’s helped me 1) meet others who have similar interests and 2) educate people about Coda.

In addition to starting your own community or meetup, make it a point to join other meetups with other like-minded people. There are most likely many remote work meetups where people who are new to remote work can discuss strategies for succeeding as a remote worker (including the tips from this article!).

5) Set boundaries for work and personal life

A common theme that comes up again and again in the remote work world is mental health. When you’re working in an office, everyone checks out at a certain time of the night and activity on Slack and e-mail slowly die down. As a new remote worker, you may be working in a new timezone, and may be adjusting to the new meeting times that don’t fit within a traditional 9-to-5. It’s really important to set boundaries for when work begins and ends for you because of the flexibility remote work gives you.

This is especially true if you don’t have a co-working space or separate office, and you work from home. If it’s getting close to dinner time and you’re still working, what’s preventing you from ordering takeout and just eating at your desk? The barrier to doing this is super low and before you know it, you’re working right up until you go to sleep. Avoid the burnout that many remote workers face when they don’t have a grasp on the number of projects they can take on. Your mental and physical health should be your number one priority!

Hopefully these tips will help you succeed as a new remote worker. If you have any questions or comments, feel free to leave them below!

Simpler, cleaner, faster. Coda 2.0 is ready for your team.

Al Chen — Thu, 24 Oct 2019 14:06:58 +0000

Originally posted on the Coda blog by Shishir Mehrotra

Back in February, we launched Coda 1.0 with mobile. We said that makers now had all the basic building blocks needed to create docs as powerful as apps.

Since then, we’ve been watching tens of thousands of makers build unique, surprising docs. Team leaders from Square to the New York Times made docs to scale their operations. The Figma Product Team envisioned and executed designs in Coda. Zapier built docs to create a more inclusive, interactive meeting culture. And people whose perspectives I’ve long admired, like Measure What Matters author John Doerr and Intercom co-founder Des Traynor, adapted their career insights and personal philosophies into docs.

At the same time, we’ve been listening closely to your feedback, gleaned over thousands of community posts, meet-ups, doc builds, support chats, and dinner conversations. From this we converged on a clear answer for where we should focus next: “teams.” A small word, containing a universe of complexity.

Based on aggregated data, these snapshots show team doc usage over a one-week period.

We call these “galaxy charts”ーsnapshots of a week-in-the-life of a team and their docs. It’s not surprising that teams use docs in ways that are as wide-ranging and diverse as the teams themselves; collaboration in Coda takes many forms. For example, a fully distributed tech team that runs meetings in Coda is a much different constellation than a teacher distributing lecture materials.

Today, I’m excited to introduce Coda 2.0, a simpler, cleaner, and faster doc for teams. It comprises new paid plans and a set of building blocks that address all the different galaxies of team collaboration.

Coda 1.0 was for makers. Coda 2.0 is for the whole team.

Introducing Coda 2.0

A simpler, cleaner, faster experience for everyone.

You may love how powerful building in Coda is, but your teammates just want to work and jump in and out of docs. We simplified their most common interactions into easy filters and conditional formats. No formulas required.

Even better, we turned our favorite building block compositions into drag-and-drop templates, and made them available right inside your doc. Now a teammate can add a topic voting table faster than you can say “show of hands.”

We wanted to make Coda a more inviting and beautiful place to work. The visual refresh technically started back in July when we released a fresh new canvasーwith fewer shadows and more space to think and plan. And it continues today with centering for readability and section headers to frame information.

The surface is also faster and more responsive, thanks to a ton of performance improvements. You’ll notice scrolling, calculations, searching, and section-switching are all smoother and faster.

Giving teams the space they need.

We saw a lot of makers and teams quickly outgrow their doc lists. With dozens of docs, shared among different teams, it became hard to organize all their Coda docs in a simple list. Today we’re launching workspaces and folders so you can now create the right space for all your team’s docs.

You can think of folders like shared doc lists. So when someone joins the team, rather than add them to every doc one by one, just add them to the team folder and they’ll get access to all the docs automatically. You’ll also see a “My Docs” folder, which is your private sandbox or working area. When you’re ready to deploy a doc to your team, simply drop it into the team folder.

Building blocks for serious teams. (Cross-doc is here.)

Now for your most requested features.

Coda 2.0 also includes Locking at the doc and section level, so teams can ensure important information stays put. On top of that, we’re adding Permissions (coming soon) so makers can ensure people are only interacting with the information they should be editing. So you’ll never have to worry about accidentally deleting your company OKR table.

And by popular demand, we have Cross-doc. We built Packs to integrate Coda with the other apps your team uses. And the number one Packs request wasn’t for another service, it was for another Coda doc! Cross-doc lets you pull data from one doc into another, so you can retain that ever-elusive single source of truth.

Cross-doc is no simple thing, and consider this a first iteration. We expect this building block in particular will likely reshape how you organize information in Coda. Instead of one megadoc with views for every team, you could now have one Coda doc for the master data set, feeding into separate team docs.

And speaking of things cross-wise… we also have good news for your teammates who don’t use Chrome: Coda 2.0 includes cross-browser support. We now work on Chromium, Safari, with Firefox in beta.

Pricing for teams of all sizes.

First of all, you’ll always be able to make docs as powerful as apps for freeーwhether that’s a to-do list, a meeting doc, or a wiki. As your docs evolve into bona-fide team tools, you’ll want to level up to a Pro, Team, or Enterprise plan.

We’ve taken an unconventional approach to pricing, which we expect you’ll find fairer and cheaper than traditional models. Most of our competitors charge for everyone using a doc, even those who only touch it lightly. We only charge you for the people who make the docs. We call it Maker Billing. Learn more here.

With Coda 2.0, we’re as convinced as ever that the maker generation is going to shape how work is done, and redefine what enterprise software looks like. Regardless of job titles — product managers, teachers, and event planners — there will always be a person who steps up to make the tool for their team. But to paraphrase John Donne, no maker is an island. With 2.0, our goal is to convert more contributors into makers, and empower the whole team to join in the making.