Every job has those mind-numbing repetitive tasks.

Chances are, if your work involves a computer, a whole bunch of those tasks involve the venerable spreadsheet.

Spreadsheets are an essential tool for anyone that works with data, and have remained so for decades despite many more new-fangled apps coming in to try and supplant them.

A great deal of repetitive spreadsheet work can be automated, but spreadsheets have so many features people don't know where to start - this guide shows you where to begin

How to Extract and Import Data

Ah, copy-pasting data from the internet - a tedious task that everyone with a computer has had to do at some point.

This is a task very amenable to automation, and as such there are a few different approaches.

Data Import Google Sheet Functions

If your data is well structured, there are a range of native sheet functions that may get the job done.

In particular, to extract data from websites this way, you’ll need to have a structured HTML table or list.

These functions are particularly useful if you’re importing data from feeds and files, available on a URL.

Formula	When to use	Documentation Link
IMPORTHTML()	Public HTML Tables or Lists	Docs (opens new window)
IMPORTFEED()	RSS Feeds	Docs (opens new window)
IMPORTDATA()	Public .csv or .tsv files from a URL	Docs (opens new window)
IMPORTXML()	Public XML Feeds	Docs (opens new window)

If the data is not publicly available (e.g. behind a login), and in particular if the data is not structured in a simple table, you’re going to need a dedicated tool, as Google sheets alone won’t be sufficient.

Use No-code Scraping tools

If the website was complex, a few years ago, you’d need to write code to extract (or scrape) data from it. No longer!

With modern no-code tools, you can now visually select the data you need to scrape.

This has many advantages:

1. Get data behind a login
2. Get data with more complex structure (i.e. not just a HTML table)
3. Group data as you need

You can see how to get started with Axiom’s no-code scraper here:

https://axiom.ai/docs/extracting-content-from-web-page.html

Axiom does a lot more than scraping, and you may want a simpler, scraping-only solution.

If axiom doesn’t work well for you, try the following dedicated scraping tools:

https://www.minirpa.net/ (opens new window)

https://simplescraper.io/ (opens new window)

Automate repetitive actions - use Macros

In Sheets

Macros are one of the most powerful features of Sheets and Excel. A macro is a way to record your user interface actions, and replay them to automate repetitive tasks.

You can use Macros to automate any UI steps that you perform repeatedly.

Example - Formatting Styles

If all your tables looks like this:

But you want them all to look like this:

You can create a Macro and assign it to a keyboard shortcut. Press the keyboard shortcut every time you see a table and it’ll format it. Make as many as you need. Convenient, right?

Here’s how you do it:

• Click: Extensions → Macros→ Record Macro
• Now perform the UI actions you want to automate. This could be anything from using the keyboard to copy and paste, or pointing and clicking on something with the mouse. In this case:
  1. Select all cells, Ctrl-A
  2. Applying alternating colors to the cells . (Click the menu item which does this)
• Click ‘Save’ and give your Macro a name and number.
• Press: Ctrl + Alt + Shift + 1 (or another number) to format tables any time!

Macros can get very complicated.

If you can, our recommendation is to make a series of small macros, rather than one huge super-macro. This is both more flexible and easier to implement.

Automate Actions on any website

Macros are powerful, and using them may give you a few ideas… sadly, they only work within the context of Google Sheets. If you would like to automate the UI of any other website in a similar way, you can do that with axiom:

https://axiom.ai/docs/automating-the-ui.html

How to format data with Google Sheet formulas - A cheat sheet

You can spend a lot of time in sheets formatting data to be presentable, particularly when you’re presenting that data to clients.

These functions in particular can be extremely useful in automating data formatting and organisation:

Formula	When to use	Documentation Link
REPLACE(text, position, length, new_text)	String replace all parts of text (a string), with another string. Probably the handiest function out there for cleaning data.	Docs (opens new window)
CLEAN(text)	Removes all non-printable characters.	Docs (opens new window)
CONCATENATE('Welcome', ' ', 'to', ' ', 'Sheets')	Join text in separate columns together	Docs (opens new window)
SPLIT("Alas, poor Yorick","")	Break text into parts, with a delimitter (e.g. a space, or comma)	Docs (opens new window)
PROPER(text_to_capitalize)	Use proper capitalization. Great for presentations!	Docs (opens new window)
TRIM(" lorem ipsum")	Trims unnecessary spaces in text.	Docs (opens new window)
VLOOKUP(search_key, range, index)	Searches a range in a column for a value, returns that value if found. If you have your data ordered in one way, and you need to reorganise it into another order using one column for the ordering.	Docs (opens new window)

How to Template Google Slide Reports from Sheets Data

There’s a good chance that, if you are collecting data in Sheets, you will want to create a report from the collected data. A great option is to make a report in Google Slides.

We’ve seen customers:

• Presenting analytics data, like monthly results
• Presenting candidate packs in recruitment
• Presenting and consolidating data in graphs

Our customers tell us they copy-paste the data back and forth from sheets to slides, doing repetitive work to generate a new report each time. They ask if Axiom can help.

It can and does help with the data collection. After that we advise to create a reusable template that links data to sheets.

Everything in this example can be linked to a data entry in a Google Sheet. This means that every month, all you need to do is:

• Update the sheet data
• Click two buttons, then a new presentation will be generated automatically!

Here’s how to do it:

• In your slideshow click Insert —> Chart —> From Sheets
• Style your charts Into the format you need.
• By default, the presentations don’t update by themselves - this is a good thing actually! It allows you to:
  1. Update your data in sheets
  2. Update your data in slides - only when required (See below)
  3. Make a slides copy, or download a file. The data in your copy/file is now a ‘snapshot’ of sheet data, at the point when you chose to save the copy.
  4. Repeat 1 -> 3 to generate as many copies as you need to generate a series from the template. e.g:

  - (January, February, March) 
  - (Candidate Set 1, Candidate Set 2, Candidate Set 3)
  

Update your data in slides - only when required

• Tools —> Linked objects —> Update All
• Then, File—> Make a copy —> Entire Presentation to make a copy with this snapshot of data

Automate Data Entry Actions with Google Sheets Data

Most spreadsheets do not live in isolation - they form part of business processes.

Every row added to a sheet may correspond to a new action you need to perform, like:

• Receiving an order → then sending it to your fulfilment partner
• Signing up a new customer → then adding them to a CRM

Or maybe the sheet is the output of the action, like:

• Receiving requests via email → then adding the data to sheets

Any action you need to do with your sheets data can be broken down into either API actions, or user interface actions. Zapier covers APIs, and Axiom covers UI actions; all bases covered with just two tools!

Zapier - API Actions

Zapier is ideal for cases when your data has an API and Zapier Connector.

When a new row is added to sheets, you can trigger a range of actions, like:

1. Sending a templated email
2. Creating a new calendar event
3. Adding a CRM entry in hubspot

You can link your sheet to any action that Zapier supports, in fact. There’s a huge library of integrations to explore within Zapier.

Axiom - Enter data into forms

If you need to populate web forms with your Google Sheets data, this is what axiom was designed for.

We have a detailed set of guides to cover just this topic!

https://axiom.ai/docs/using-google-sheets.html

https://axiom.ai/docs/automating-the-ui.html

Concluding

We’ve only just scratched the surface of Sheets. As of 2022, Spreadsheets have been around for 53 years, and we bet they’ll still be around 50 years from now, too.

We’ll be revisiting this topic again in the (near) future.

Vist axiom.ai/customer-support if you need some help integrating axiom automations with Google Sheets!