Loading
Loading
  • Home

  • Productivity

  • App tips

App tips

25 min read

Spreadsheet CRM: How to Create a Customizable CRM with Google Sheets

By Michael Grubbs · July 13, 2016

When VisiCalc came out as the first consumer spreadsheet software in 1979, it brought the number-crunching capabilities of big business to everyone. It was the first time anyone could harness the power of automated calculations to manage their own data.

Spreadsheets gave us the potential to input, modify, and calculate anything we wanted and store it digitally for re-use. You could build anything from run-of-the-mill grocery lists to financial models that feed off of massive data sets—the possibilities seemed endless. Spreadsheets were the original killer app, one of the main reasons you'd want to own a computer.

And now, you can use spreadsheets to build your own apps. With internet-connect spreadsheets like , you can format and analyze data with standard spreadsheet tools, and also pull in data from the web and run complex workflows automatically.

We'll learn how to do just that in this chapter, by building a full-featured, automated contact management system with just a spreadsheet.

If you're just getting started reading this book, be sure to jump back to Chapter 1 for a tour of Google Sheets' core features, and check chapter 2 to learn more about Google Forms. You'll need the info from both in this chapter.

Tools to Build Your Google Sheets CRM

This tutorial tells you everything you'll need to build each part of the spreadsheet. But sometimes, it's easier to follow along with a pre-made spreadsheet. For that, we've included a template with every example featured in this tutorial—all you have to do is copy it to your account.

Just open the template in Google Sheets, then copy it to your own Google Drive account by clicking File and selecting Make a Copy.

copy spreadsheet

You can also take this sheet, tear it apart, and modify it so it suits your needs. Each use case is implemented in its own tab, so you can see how they would potentially interact to form a complete contact management system.

I recommend either following along and tinkering with each element as it’s brought up. But if you're feeling brave, you could build your own spreadsheet from scratch. The latter will help you really get the feel for creating in Google Sheets rather than just using it.


How To Build a CRM in Google Sheets

  1. Add a Form to Your Spreadsheet

  2. Create a Contact Management System

  3. Qualify Contacts with Web Scraping

  4. Build an Outreach System with Social Media and Email


Add a Form to Your Spreadsheet

In this tutorial you will learn: How to make a form and spreadsheet in Google Sheets, and move data around automatically.

One of the most significant "Aha" moments you’ll have with Sheets is realizing that your spreadsheets are connected to the internet—which means they can do work for you online. Google Sheets can automatically import data from other websites (something we'll look at later on), and it also includes a form tool, Google Forms, to gather data and save it directly to your spreadsheet.

We looked at Google Forms features and tools in chapter 2—jump back for a refresher if you haven't made your own forms in it yet. Now, let's try it out by setting up a form to collect lead information from prospective clients.

You could embed this form on your site, add it with button or link on your website, or share a link to the form via social media or email. When people click on your link, they can fill out your questions, and the data will be saved to your spreadsheet automatically.

1. Set up your form

First, you'll want to add a form to your spreadsheet as a simple way to gather data. Here's the Form example I created—try it out and submit your (fake) data!

To implement your own form, you’ll first need a spreadsheet (of course).

If you didn’t copy the tutorial template, go to your Google Drive account and click New > Google Sheets as in this screenshot.

create sheet

Once you have a sheet open, click Insert > Form and a new window will pop open where you will start filling out your form information.

add form

2. Change your form's settings

This section allows you to choose who can see the form, and how many responses each person is allowed to submit. You can also customize a few aesthetic options, like question order and progress bars.

If you’re using Google Apps for Work, you can set it to only allow users from your organization and automatically collect their ID when they submit the form. This feature is especially useful for internal data entry, or for outsourced freelancers entering data from assigned company accounts.

Google Forms Settings

For this form, I will leave all of the preferences blank so that it’s accessible to you (and everyone else), and allows unlimited responses per person. If you're making a generic contact form as an easy way for people to get in touch with your team, you'll likely want the same settings.

3. Add some questions

The questions you'll add to your form will depend on the data you want to collect, so think through the questions you'll need for your own form. Try to collect data in a way that will make it easy for you to manipulate it later—ask generic questions with each answer in its own field.

For example, rather than allowing a user to enter a free-form answer to a question, offer 3-5 fairly comprehensive text-based choices in a multiple-choice question.

form questions

Just enter the questions you want, pick the answer type, and Google will automatically create rows in your spreadsheet for each of your form's questions. You can also add some useful logic to the form with sections to personalize the questions to a user’s responses. Jump back to Chapter 2's Google Forms Logic Sections guide if you need a refresher.

Here are some sample responses to my demo form for this tutorial:

responses

As you can see, complicated text answers are difficult to read and also difficult to use in a meaningful way without directly analyzing the content. Some situations make sense for long-form answers (like when you expect only a few responses and have time to read them all). But when you’re expecting the system to run in the background, you won't want to analyze everyone’s submissions.

That's why it's best to make sure each question will only get a one-word answer—that gives you a lot more flexibility.

So now you have some data, what do you do with it?

Well, you can use it "as is" to get in touch with your new contacts, but let's take it a step further.

4. Move your data to another sheet for processing

I’ll start by re-naming my form response sheet. The form originally shows up as a sheet named "Form Responses 1" but I renamed it "Customer Data" by double-clicking the name at the bottom of the worksheet.

rename sheet

Once you have changed the Form Responses sheet's name to something more appropriate, we can take the information in that information and put it into another sheet. You can reference data from another sheet quickly by using the =INDIRECT() function.

Here's what the formula would look like for the form we just created:

=INDIRECT("Customer Data!A2:H")

  • Customer Data is where any new form submissions are saved.

  • ! is used to reference another worksheet in the same spreadsheet.

  • A2:H is the range of cells containing the data I’d like to reference—and it will automatically include all new data that comes in.

Alternately, if you want to keep responses separate from where you will perform data manipulation, you can use the =IMPORTRANGE() function in a completely different spreadsheet (a different document).

The formula to import the data into a separate spreadsheet would look like this for my example:

=IMPORTRANGE("1TOvmNLEhURU5YlVoL2wTJ6-LrXT4hco7gNKwW2a1Sh8","Customer Data!A2:H")

  • 1TOvm....wW2a1Sh8 is the "Spreadsheet ID" located in the original spreadsheet’s URL (check the URL bar in your browser—it's the text after the /d/ in the address)

  • Customer Data!A2:H references the range of the original data to import.

When you enter this formula, you'll have to authorize the new spreadsheet to access the data—just click the "Allow access" button that'll pop-up beside that cell.

authorize

Click "OK" and your data should show up after a few seconds.

Now you can access form submission data in any of your spreadsheets and worksheets!

From here, you can do all manner of things. I’ll demonstrate a use case by creating a simple Customer Relationship Management system (CRM) based on the form we just set up.


Create a Contact Management System

In this tutorial you will learn: How to make a CRM in Google Sheets.

Managing customer and contact data in a spreadsheet is a time-tested business practice. You’ll often hear modern data management solutions talk about "ditching spreadsheets." But I love using Google Sheets for managing this type of data: it makes moving and organizing data extremely simple.

If you don’t already have a contact management system in place, you'll want to collect data like:

  • Contact information, including each person's name, phone number, email, company, website, and social profiles.

  • Source of the lead or customer (website signup, social media, paid advertising, etc)

  • A system or code for their lead nurture stage.

  • Customer Value (single purchase, lifetime value, or whatever is appropriate).

  • Information about interactions (dates, topic, significant notes, etc).

You’re probably already keeping track of some of this data, but a detailed spreadsheet CRM can help you store more of it in a simpler format.

Google Sheets eliminates many of the distracting features of a full-fledged CRM while allowing for a level of customization that you won’t find in an off-the-shelf solution.

The caveat, of course, is that you have to build it. But don’t worry: it’s easy and a great way to practice your spreadsheet skills.

1. Label some columns

Be sure to add a column for any information you want to keep track of. Per my example bullets above, I’ve gone ahead and labeled my columns as shown below:

column titles

You may notice the "ID" and "Updated" columns I’ve created. The "Updated" column is automatically created with every form entry, so I want to include that data in case I want to do something with the age of the data in the future.

The "ID" column is a part of the next step...

2. Manually enter or import data

The point of a CRM is to track contact data and customer lifecycle changes in a format that you can query later.

In the previous section, I showed how to set up a form to collect data from an outside source (rather than via one of your team members or direct collaborators). The form we created was designed for prospective new customers, though it can just as easily be used by myself or any member of my team to quickly input the data in a structured way.

In this CRM example, I’ll use the data entered into that form as the customer entries in my CRM sheet. Then I’ll assign an ID to the imported customer and choose a team member to be assigned to the customer based on their prospective monthly spend (a data point in the form).

We’ll use 4 functions to accomplish this:

  • =ARRAYFORMULA() - to automatically update the IDs when new entries are added.

  • =CHOOSE() - to select a team member based on the monthly spend.

  • =ROW() - to grab the number of the row where the data is located.

  • =IF() - for setting conditions on when to make things happen.

In order to keep your functions separate from the data (which you might be copying and pasting in the future), I recommend creating a row below your headers specifically for your formulas and hiding it when not in use. (I’ve highlighted mine red.)

formulas row

You will hide this row by right-clicking on the gray area to the left with the row number on it, then selecting Hide Row. If you need to add or tweak these formulas (which you will shortly), select a range of rows that includes the hidden row and Right-Click > Unhide Rows.

Before I set up the ARRAYFORMULA(), I’d like to make data ranges easy to reference in my formulas.

3. Name your data ranges

This allows you to use a short name like "name" instead of A:A every time you’d like to access that range of cells.

You don’t have to do this; sometimes I prefer not to name ranges so I know exactly what data I’m looking at. But, for times when you don’t plan to change the range very much, it’s a good practice that simplifies things.

To define and name an entire column, right-click on the gray box with the letter at the top of the column and select Define Named Range. Click "Done" once you’ve ensured it’s the right range.

range

Do this for all of your main data ranges (in the image I just showed a large range of cells as an example). You can now access those ranges simply by typing the short name you used when naming them (do note that it's case-sensitive).

4. Manipulate your data with formulas

To do that, let's set up an ID for each new entry.

You might wonder why you need an ID when the row clearly has numbers next to it.

I do this because if I ever want to filter the data and export a shortened list of specific entries, the row number doesn’t stay with the data between sheets or exports to CSV files. So if you decide to upload this information to a "real" CRM at some point, you normally wouldn’t be able to keep track of the customer’s entry number.

Having a dedicated ID for each entry solves that problem.

To add a Customer ID, use this formula in your "ID" column’s formula row (the one you’ll be hiding):

=ARRAYFORMULA(if(name<>"",ROW(name)-3,""))

  • if(name<>"" checks to see if there’s a name in the "Name" column

  • ROW(name)-3 returns the ID based on the row number -3 (this is to offset the number of header rows that are used to title / hidden formula row).

  • The "" in the 3rd argument of the formula is to avoid returning "FALSE" in every row below the most recent entry. It’s purely aesthetic, but I find that I can recognize a blank cell more easily than sorting through a bunch of "FALSE"s mixed with my data.

5. Assign your customers to sales reps

In the "Assigned" column’s formula row, we’ll insert the following:

=ARRAYFORMULA(IF(value<>"",IF(value = "0-500", "Mike",IF(value = "500-1000", "Matt", "Danny")),""))

  • IF(value<>"", again checks if there’s a CRM entry by looking at the value column

  • IF(value = "0-500", "Mike", returns "Mike" if the value is "0-500",

  • IF(value = "500-1000", "Matt", assigns anything that matches "500-1000" to "Matt"

  • "Danny")),"")) returns "Danny" as the default case and the last "" returns blank if nothing is in the value column

Awesome! So now there is an agent assigned to each new, ID’d entry into your CRM.

Here’s what would happen if you add someone now:

auto add ID number

If you’d like to modify customer data, you would do so in the "Customer Data" sheet, rather than in your CRM. This is because CRM sheet references the data from the "Customer Data" sheet via formulas. So rather than changing the actual data, if you modified the information in the CRM sheet, you would break your formulas!

If you want to add additional information manually, you can add more columns to the right of your last data-filled column. That's the best way to keep from breaking things. For example, you could create a "Notes" column in column K to add additional information on any new customer.

6. Set up a way to add customers manually

You don’t want to manually enter each piece of contact information. Instead, let's place a link directly to the input form on your CRM sheet. This way, you can just click the link to pop open the form, fill in the data as if you were a customer, and submit it.

I placed my form link in a new row at the top of my sheet using the =HYPERLINK() function.

form link

To start, you’ll need the link you would share with someone who you wanted to fill in the form for this spreadsheet, such as this link for this tutorial's example form.

This sharing link can be found by clicking the "Send Form" button on your attached form’s editing screen or by clicking Form > Go To Live Form from the worksheet’s toolbar and copying the URL.

Once you have the link, go back to your spreadsheet, select a cell that you want to place the formula in, and make yours look like this (but with the URL for your form):

=HYPERLINK("https://docs.google.com/...form#start=invite", "ADD NEW")

  • =HYPERLINK("https://docs.google...form#start=invite", is the URL to the form. We're using the ... to represent redacted characters from our URL. You'll want to paste the entire form URL.

  • "ADD NEW") is the text you want to show up in the cell.

To use the link, hover over the cell and the URL will pop up above it, allowing you to click it.

Go ahead and submit some information through your form and view the results in your CRM sheet—you should see the new data show up almost instantly. It’s incredibly satisfying to have the ID and Team member assignment happen automatically!

What about a more complex (and incredibly awesome) way to collect additional data about these new potential customers?


Qualify Contacts with Web Scraping

In this tutorial you will learn: How to pick most elements from a web page and grab their contents

Adding some automated data scraping—copying data from other websites automatically—to your sheets is as easy as adding a few built-in functions. You can grab almost any data you want from most URLs by using the IMPORT functions in Google Sheets. Those include:

  • IMPORTXML() - Imports a site’s XML markup.

  • IMPORTDATA() - Imports the content of a page saved as a .csv or a .tsv on a site’s directory.

  • IMPORTFEED() - Import an RSS or ATOM feed.

  • IMPORTHTML() - Imports a page’s HTML tables.

  • IMPORTRANGE() - Imports data from another Google Sheet.

Continuing along with our CRM example, let’s see if we can get some more information about these new prospects automatically.

If you didn’t enter a valid URL for your contacts' website into any of your form entries, you will want to either change some URLs in your "Customer Data" sheet now, or enter in a few more manual form submissions with valid links.

1. Choose data to scrape

You may not know this, but each website is coded slightly differently. There are loads of best practices and shifting standards, but it’s pretty rare that the guts of one site are exactly the same as another.

This is especially true of elements within the <Body> tags of a webpage.

For example, the search bar on Google’s homepage is tagged and coded differently than the search bar in Zapier’s help section. The HTML elements are different, the styling is different, and the scripts that power their interactions with users are different (so they get lots of different tags applied to them).

These differences make the elements’ Extensible Markup Language (XML) paths different, too. The XPath (shorthand terminology) helps you find each similar element on a given page based on its HTML structure and tagging. Google Sheets can use this structural information to grab the contents of a webpage—so, for example, if you have the XPath of the Twitter bio, and a link to each of your contacts' Twitter page, Google Sheets could import each person's Twitter bio automatically. All you'll need to do is find the XPath.

One easy way to see the XPath of any element on a page is to use Chrome’s Developer Tools (CMD+OPT+i to open) or just right click and select *Inspect Element on whatever you want to see the path for. This will first Open Dev Tools and then highlight that element in the "Elements" section.

Once you see the gray or blue highlighted element, right-click on it and select Copy XPath to copy the exact XPath of that element to your clipboard.

copy xpath

Here’s what you’d get if you were to paste the results of the "Copy XPath" command from the Developer Tools pane:

//*[@id="lst-ib"]

This is the super-specific, non-generalized way to reference that element, and it’s only really useful for that specific element.

Depending on the element you are trying to scrape (and if you want to apply the formula to multiple sites) there are some XPaths that are the same across most sites.

One example of these standard elements is site "Meta Tags". These usually Search Engine Optimization (SEO) elements like the Title, the Description, and Author of the page, and can hold a bunch of useful info. They're your best bet if you want to copy data from a wide range of different websites.

We’ll use these Meta Tags to collect additional information about our leads’ website or service to help better aid the sales representatives that we assigned in the CRM section above.

Want to learn more about tracking down XPaths? Read Google’s documentation for more help getting started with Chrome Developer Tools.

2. Scrape your data using Google Sheets

We’re going to use the =IMPORTXML() function here (which is by far my favorite IMPORT() function). =IMPORTXML() makes it easy to grab similar data from different pages without knowing exactly how they are tagged or structured.

Another function we’ll be using is =IFERROR(). You can think of it as a traditional IF() that we used in the example above, but instead of putting in your own condition statement (like "If this happens then do this") that statement will always be "Check to see if this formula results in an Error when you run it".

You’ll recognize when a formula is "throwing an error" when you see a #REF!, #ERROR!, #NAME?, or #N/A as the result. Sometimes this is because of a faulty formula, but other times it’s just the result of a formula not grabbing any data. Google Sheets should tell you the source of the problem, though.

Sheets errors

This sometimes happens with IMPORTXML() because some sites block scraping. Or, the URL might be dead, producing a 404 (page not found) error.

You can hide these error codes by wrapping our formula in the IFERROR() function, which shows a different result if the cell runs into a problem.

One thing to note is that by doing this, you effectively silence any helpful errors that would let you know that your formula is incorrect. I recommend only wrapping your function in IFERROR() once you’re sure that it actually works the way you expect.

Here’s what the completed formula for scraping a page’s "Title" looks like:

=IFERROR(IMPORTXML(website,"//title"),"none")

  • =IFERROR( - wrapping our IMPORTXML( function to check for an error

  • website is the range where the URLs are located.

  • "//title" is the XML reference for the "title" element in a webpage’s code.

  • "none"' is the default result toIFERROR()if theIMPORTXML()` function fails to find a "title".

Unfortunately, IMPORTXML() has a limit of 50 instances per worksheet (as in, 50 IMPORTXML() functions in the entire document) so we need to be judicious in our application of the function.

To combat this limit, rather than creating a new IMPORTXML() for each of the Title, Meta Description and Keywords, I’ll go ahead and use the XPath "OR" operator which is | (a vertical line or "pipe") to combine the queries into a single function.

Place the following XML paths after //title, with each separated by a |:

//meta[@name='description']/@content //meta[@name='keywords']/@content

As you may have noticed, the Description and Keywords XML paths are a bit more complicated because you need to get more specific to reference the right elements.

You can read up on constructing XPath arguments from this Microsoft resource or use the W3 XPath Syntax Guide to learn all things Xpath.

The completed formula will look like this:

=IFERROR(IMPORTXML(website,"//title|//meta[@name='description']/@content|//meta[@name='keywords']/@content"),"none")

We want to list our results on the same row as our contact in the CRM (rather than spilling vertically into the next contact). To fix this, we just wrap the whole formulas in a TRANSPOSE() function. This will take the vertical data and flip it to spill into the cells to its right.

Here’s the final, FINAL version of the scraping formula:

=TRANSPOSE(IFERROR(IMPORTXML(website,"//title|//meta[@name='description']/@content|//meta[@name='keywords']/@content"),"none"))

And it will look like this in your formula box:

scraping

NOTE: I’m still putting these functions in my 3rd row, which is acting as my hidden formula row.

3. Copy your formulas down to any new contacts

We used ARRAYFORMULA() in the Form and CRM sections above to automatically apply formulas to new entries in our CRM. That doesn’t work with IMPORTXML(), due to that "50 instances" limit mentioned above.

Because the ARRAYFORMULA() doesn’t actually create new formulas in subsequent rows (but rather just applies the formula to the next row in the range), you MUST copy your formula down for each time you want to use IMPORTXML().

You can do this by grabbing the small box that shows up in the bottom right of any cell with your scraping formula in it and dragging the box down over the new rows with contacts to be scraped.

Here’s what the process looks like:

dragging

What happens when you hit your 50th contact? You can’t scrape anymore unless you remove some of the old IMPORTXML() functions. I usually do this by highlighting all of the cells that receive results from the IMPORTXML() formulas then right-clicking in the uppermost left cell of the highlighted range and selecting Paste Special > Values Only.

If you try to paste normally, you will only paste the formula in each cell (which would definitely exceed your 50 IMPORTXML() limit)—but pasting the values only just preserves the text you already copied from the websites.

paste special

You shouldn’t see anything change once you paste, but you’ll notice that if you double-click any of those cells, the formula is gone and only the text data remains.

This process is especially useful if you want to grab data that has been manipulated by ANY formula in your sheet and paste it to another sheet or document altogether.

TIP: Leave the last row with the IMPORTXML() function out of this process. This way, the next time you need to drag the formula down over "new" contacts, you can start the next scraping at the most recent contact (rather than copying the formula from the hidden formula row and pasting into the cell).

Think you might have a hard time knowing where the "last scraped contact" was? Let’s make it easier to see where old contacts end and new ones begin with some highlighting via conditional formatting!

4. Highlight new contacts with a Conditional Formatting rule

You can use this trick to highlight any type of data you want in your sheet. In this case, it functions purely as a boost to the usability of your CRM, but you could also use Conditional Formatting to quickly identify VIP prospects based on any set of conditions like date created, keywords in a cell, or numeric comparisons. You can also use this formatting to literally "format" your text with bold, colors, decoration, and a number of other visual changes.

To start, in the top-left corner of your sheet, there is a gray box that is neither a row nor a column.

If you click this, it will highlight EVERYTHING in the sheet.

By using Right Click > Conditional Formatting on this box, you will both select the entire sheet as a selected range and pop open the Conditional Formatting sidebar to the right.

conditional formatting

Here, you can define how you’d like to format based on a number of text-based conditional statements and custom rules.

I’ll use the "Custom Formula is" at the bottom of the first drop-down.

In the formula box, we’ll put this formula:

=AND($C:$C<>"",$K:$K="")

  • =AND( returns TRUE or FALSE based on both logic statements.

  • $C:$C<>"" checks to make sure there’s a CRM entry by looking at the Name column.

  • $K:$K="" checks to see if that contact has had their Meta info scraped by looking at the first column in the "Meta Information" columns.

Then you can select whichever color you want to use for the highlight!

highlight

You may notice that in the formula we had to use the actual column letters and Dollar $ signs in the Custom Formatting formula. This is required to color the whole row rather than just a single cell. Check out Google’s Conditional Formatting reference for more information on why this is.

One last thing to add is a column that will be used to simply know whether our agent made contact.

Create a column and label it however you want (mine is "Contacted"). Then decide how you’d like to "check off" if the customer was contacted. I will use a simple code of Yes (y) / No (n).

Here’s how some example data may look in this field:

yes no

So now we’ve captured a lead or customer, assigned them to a representative, and have qualified them with some additional information. We’ve also set it up so that new entries will be highlighted, so we know to scrape them right away when we load our CRM and have given our team a way to record if they’ve made contact.

The next phase of any good relationship is follow-ups! Why don't we set up some social management and outreach via Twitter?


Build an Outreach System with Social Media and Email

In this tutorial you will learn: How to build an automated email and Twitter followup system in Google Sheets.

It’s pretty common to include social media profiles in your CRM. In the Form and CRM examples above, we collected each prospect's Twitter handle at the time of data entry. Let’s assume that everyone fills out their handle correctly (and that everyone has a Twitter account) and set up a Twitter outreach tool to accompany our CRM.

This sheet will be able to:

  1. Automatically create Tweets addressed to the contact’s @TwitterHandle

  2. Include a reference to their personal sales contact

  3. Check to see how long ago the customer signed up

  4. Send the Tweet to the prospect with 2 clicks

  5. Automatically email the prospect as well, 3 days after your call

1. Import data into your Social Media sheet

We don’t need to use every data point from the CRM sheet. For this social media example, we’ll use the named ranges added (date added), name (customer name), twitter (@handle), and assigned (the team member who will get in touch).

We need to target each range with a formula. Using ARRAYFORMULA() and setting its parameter to the Named Ranges we set up earlier, we can quickly pull specific ranges.

We’ll begin by importing the ranges into the "Social Media" sheet:

Name:

=ARRAYFORMULA(IF(twitter<>"",name,""))

  • =ARRAYFORMULA(IF(twitter<>"", checks to ensure the contact has a Twitter handle in the CRM and makes it apply to all future rows.

  • name,"") returns the contact’s name if the IF() was true, otherwise it returns "".

Twitter:

=ARRAYFORMULA(twitter)

  • Returns the "twitter" named range.

Assigned:

=ARRAYFORMULA(IF(twitter<>"",assigned,""))

  • Same explanation as the "Name" column, except it returns the assigned range, which was the team member instead of the contact’s name.

Here’s what your "Social Media" sheet will look like at this point:

twitter sheet

2. Combine your CRM data with a dynamic Tweet

With the basic CRM data imported, we need a Tweet that we can insert the data into.

In either your column header row or the dedicated formula row (the one highlighted in red), break your Tweet into sections that would surround your imported name, twitter, and assigned data.

In this example, I break it down like this:

twitter sheet

(twitter) > Greeting > (name) > Main Text > (assigned) > Closing Punctuation

You can structure this however you want, placing your dynamic content in any number of creative phrasings that include links, hashtags, and more.

Now we need to combine all the parts into one cell. This happens in the "Tweet Copy" column.

Here’s what the completed formula looks like:

=ARRAYFORMULA(IF(twitter<>"",twitter&$J$2&name&$L$2&assigned&$N$2,""))

  • =ARRAYFORMULA(IF(twitter<>"" checks to see if the customer has a Twitter handle on file and makes the formula apply to all future rows.

  • twitter&$J$2&name&$L$2&assigned&$N$2 combines each of the CRM data points with the "chunked" Tweet we created in the formula row. The & sign is for combining two text values with each other in one cell.

Here’s an example of what you get:

@moblized1234 Hey Mike Grubbs, Thanks again for connecting! How was your chat with Danny?

tweets

You can either hide your "Tweet Parts" section or leave it unhidden so you can make quick changes.

Now, you can do one of two things:

  1. Copy the text and paste it into your Twitter client of choice and send the Tweets one-by-one

  2. Make your life easier and send your Tweets (nearly) directly from Google Sheets

Let's do the latter!

3. Add a "Tweet This" link for each Tweet

Since we’re managing customer information from our sheets CRM, it makes sense that we could interact with the customer directly from the spreadsheet.

We can accomplish this by using a Twitter "Intent" URL, which lets you pre-populate a Tweet with a string of text in the URL.

A Twitter Intent URL is structured like this:

https://twitter.com/intent/tweet?text= + anything you want to say!

You could use this URL anywhere to quickly send Tweets without jumping into Twitter. Instead, what happens is that when you click the link it will either pop-up a window or open a new tab to quickly publish your Tweet—no typing required!

Twitter popup

TIP: You could also use this trick to create Tweet-able links in your blog posts for people to "Tweet This" and help drive up engagement / shares.

In this example, we’ll wrap the Twitter URL with another HYPERLINK() function (just like the "Add New" customer from the CRM section).

Here’s what the complete formula would look like:

=ARRAYFORMULA(IF(twitter<>"",HYPERLINK("https://twitter.com/intent/tweet?text="&E2:E,"Tweet This"),""))

  • =ARRAYFORMULA(IF(twitter<>"", checks if the customer has a twitter in the CRM sheet.

  • HYPERLINK("https://twitter.com/intent/tweet?text="&E2:E, adds our completed dynamic Tweets in column E to the end of the Twitter Intent URL.

  • "Tweet This"),"")) sets "Tweet This" as the text to show in the cell, and "" is what is returned if there is no twitter account for that contact row.

One of the most frustrating things about trying to automate any Twitter task is the character limit. Especially when you are dynamically generating Tweets in this way, you’ll want to know how many characters are in your Tweets so that you can try to account for the variability of the input data.

4. Add a character counter to monitor Tweet length

This counter serves 2 purposes:

  • To let you know how long your dynamic tweets are on average (to adjust your Tweet parts to be more forgiving of long client names, hashtags, etc).

  • To use as a point of verification before automatically sending the Tweet.

First, define the Tweet Copy column as "tweets" using the Right Click > Define Named Range function with rows D2:D highlighted. Now you can reference it just by typing "tweets" in your formulas.

The formula for counting the length of our completed dynamic Tweets will look like this:

=ARRAYFORMULA(IF(twitter<>"",LEN(tweets),""))

  • =ARRAYFORMULA(IF(twitter<>"", checks to see if the customer has a twitter in the CRM sheet

  • LEN(tweets),"")) uses the LEN(), or length, function to count the number of characters in the tweets column and "" is what is returned if there is no twitter account for that contact

Here’s what you’ll get:

Tweet length

NOTE: If you put a URL into your dynamic Tweets it will always count as 22 characters, no matter how long it is. Twitter treats every URL as if it were shortened, so you may need to tweak your LEN() formula to account for this.

5. Automatically Send Followup Emails

Tweets are a great way to remind your contact about the call, but an email is an even better way to follow up and continue the conversation in-depth. Let’s have our spreadsheet automatically send an email to our contacts the 3rd day after they have been contacted by our sales representative.

A simple way to do this is via a Zapier Google Sheets integration. The logic behind the Zaps will be:

When the Contact has been contacted, wait 3 days then email them.

We’ll use a multi-step Zap for this that watches Google Sheets for a new entry, then waits for 3 days and creates the email.

One preliminary step we must take is to bring our "Contacted" column into our Social Media sheet via an ARRAYFORMULA().

In this case, =ARRAYFORMULA(contacted) imports the named range contacted from the CRM sheet.

contacted or not

This is important for the next step, which relies on changes to the Contacted column to trigger our Zapier automation. You'll also need one more thing: email addresses. So repeat that step, and add another new column with =ARRAYFORMULA(email) to pull in each contact's email address.


Now it's time to make our Zap. If you’ve never set up a Zap or don’t have a Zapier account, sign up for free, then run through the guide.

Once you’ve got the hang of the interface, create a new Zap, select Google Sheets as the Trigger app, and choose Updated Spreadsheet Row as the trigger.

Watch Google Sheets for updated row with Zapier

Now you’ll need to connect your Google Sheets account if you haven't already. Then, select the spreadsheet and worksheet you want to watch—in this case, select your CRM spreadsheet, and the Social Media sheet.

You can then specify which column to watch for changes, and we want to watch the Contacted column for that. Click the down arrow under Zapier's Trigger Column field, and find the column which should be labeled gsx$contacted.

select spreadsheet and worksheet in Zapier

For the next step of your Zap, you'll add an action, select the Delay by Zapier app, and then choose Delay For. Then, tell the Zap to delay for 3 days by entering 3 in the Time Delayed For (value) field and then select Days in the (unit) field.

select delay time in Zapier

Now we're ready to make our email. Add one more step to the Zap, and this time either select the Email by Zapier or the Gmail app to send your message. Select the account, as normal, then you can setup your automated email template.

Click the + icon beside the To field, and select your Email column from Google Sheets. Add a subject, then write the body of your email message. You can include your contact's name in the body—again, using the + icon on the right to copy the field from Google Sheets—and add any other personalizations you want.

Add email address to Gmail

Name the Zap and save it to turn it on. Then, you’re done: when your sales team marks someone as "contacted," the Zap will notice the change to your Contacted column value and set your email to be sent in 3 days.

Recap of what your new system can do:

  1. Accept new form submissions from potential customers

  2. Add new people to your customer database

  3. ID the customer and assign a representative of your team to handle them

  4. Highlight new entries in your CRM

  5. Scrape more information about their website into your sheet

  6. Build a dynamic Tweet and create a "Quick Tweet" button

  7. Automatically reach out via Twitter and Email via Zapier integrations 3 days later

Pretty cool right?

And that’s just the tip of the iceberg. You can take what you’ve learned from this tutorial to create your own Google Sheets systems.

Whether you are trying to replace an existing workflow, or just can’t find a tool that does quite what you need, Google Sheets is flexible enough for the job.

Connect Google Sheets to Your Other Apps

Web scraping and adding data with forms can make some powerful-yet-simple apps to manage contacts—or anything else you want. And with Zapier's Google Sheets integrations integrations, you can make your app do more work on its own automatically.

Say you want to also add followup tasks to your project management tool, or want to add contacts to your email marketing app. Just link your spreadsheet to Zapier, and it can watch for new entries and add those to any other app.

Read more: 21 free Google Sheets templates to boost productivity

Get productivity tips delivered straight to your inbox

We’ll email you 1-3 times per week—and never share your information.

tags

Related articles

Improve your productivity automatically. Use Zapier to get your apps working together.

Sign up
A Zap with the trigger 'When I get a new lead from Facebook,' and the action 'Notify my team in Slack'