Hi Reader,

Welcome to the Google Sheets Tips newsletter #282, your Monday morning espresso, in spreadsheet form!

A big thank you to this week's sponsors who help keep this newsletter free to read:

PARSEUR Automatically import data from emails & PDFs into Google Sheets ✨ Manually extracting data from documents is a pain. That's why 30,000+ businesses use Parseur to automatically extract data from PDFs, invoices, emails, and other documents and send it straight into their Google Sheets. Try it for free

AUTOSYNC | STRIPE TO GOOGLE Sync Your Stripe Data Seamlessly with AutoSync! Eliminate manual CSV exports from Stripe with AutoSync for Google Sheets, your gateway to automated Stripe business data synchronization. Try AutoSync for Free Now!

➜ News

I.
​AppSheet smart chips can now be used inside Google Docs, Sheets, and Slides. These allow you to access AppSheet content and automation from inside Docs.

This requires a Workspace account and AppSheet license.

​Read more here​

II.
​Google recently announced a feature to create shareable video presentations in Google Slides! This looks like a viable alternative to tools like Loom when you're working inside of Slides. Perhaps we'll see it in Sheets someday too...

This is a paid Workspace feature.

​Read more here​

III.
​More new and exciting AppSheet resources for you!

Fellow Google Developer Experts Pablo Felip and Martin Hawksey have published some great AppSheet content recently:

Pablo: Discovering AppSheet in 48 Hrs Parts 1, 2, 3, 4​

Martin: Democratisation of app development with Duet AI for AppSheet​

IV.
​My business secret is out! The Growth In Reverse newsletter recently covered my business and the tactics I've used to grow it.

​Ben Collins Built a Successful Business With Google Sheets Tutorials​

(If you're interested in online growth marketing I highly recommend subscribing to Chenell's newsletter. It's packed full of great case studies and actionable takeaways.)

_______

➜ Google Sheets Tip #282: Timeline Views a.k.a. Gantt Charts

Gantt charts are a special type of bar chart, beloved by project managers, that shows activities on a schedule.

They give an overview of all the tasks in a project, set to a timeline. This way, you can see how time is allocated within a project and see what needs to happen and when.

In today's tip, we'll see 2 ways to create Gantt charts: 1) the quick way using Timeline View (which is sadly not available to everyone), and 2) using regular bar charts.

Part 1: Create Gantt Charts with Timeline View

Unfortunately, this feature is NOT available in personal Google Accounts, Nonprofits, or to legacy G Suite customers. See Part 2 on how to create a Gantt chart with a regular bar chart.

The timeline view chart is a special type of chart, under the Insert menu. To use it, you must have data in the correct format.

At a minimum, you need three columns containing: 1) a task name, 2) a start date for the task, and 3) an end date (or duration) for the task.

To create a timeline view chart, highlight your data and select the menu:

Insert > Timeline

Check the correct range is selected and click "OK".

Google creates the timeline view chart in a new sheet:

Advanced Timeline View Features

Go to the "Settings" menu of the Timeline View (the gear wheel above the Timeline View chart).

Use additional descriptive columns in the data to add grouping and labeling to the Timeline View.

The bars can also be color-coded to enhance the visibility of any applied grouping.

Add color to the bars directly in the Timeline View tool (click the bar and change the color in the "Card details" sidebar) or format the underlying data, which is then reflected in the Timeline View.

For example, let's add an "Assignee" column with colors to the data:

The Timeline View is updated to group by the assignee names and colors:

More info at the Timeline View help page.

Part 2: Create Gantt Charts with Bar Charts

I don't want to leave those of you with personal Google accounts out of the picture, so let's see how we can create a similar outcome with a regular bar chart.

Using the same dataset from part 1, highlight the data and go to:

Insert > Chart

Select the "Stacked bar chart" option in the "Setup" section:

Quick Aside: dates are stored as numbers in spreadsheets, so the date "3/15/2023" is actually the number 45,000 under the hood. (Try changing the formatting of the cell to see this!)

You might notice that the chart axis starts from 0 and goes all the way up to 50,000. This means that the "duration" series is too small to see. We need to fix this axis to start from the first date "3/15/2023", a.k.a. the number 45,000.

So we need to know the number of the earliest date in the dataset. To find it, either format the date cell as a number and make a note of it, or use this formula =N(B2) to get the date as a number.

Under the "Customize" section, set the following options:

• Horizontal axis --> Set the "Min" to the date number (e.g. in my case 45000 to represent 3/15/2023). This ensures that the x-axis starts at the beginning of this first date.
• Legend --> Set "Position" to "None"
• Series --> Set the "Fill opacity" of the "Start Date" series to 0% to hide the bars

And there we go! Our bar chart now looks like a Gantt chart / Timeline View.

_______

If you enjoyed this newsletter, please forward it to a friend who might enjoy it.

Have a great week!

Cheers,
Ben

P.S. Some Google Sheets Christmas ideas from last year...​