In the early days of computing, the spreadsheets programs when initially brought to the scene were revolutionary. To the extent that, even today they continue to be an integral part of our work life. Like everything else, they have evolved into much more powerful, robust and advanced applications. And in the current information age, they are made even more user-friendly, in the sense that they can be accessed from anywhere and any device. Google Sheets has been the on the forefront of this innovation. Yet, not all ‘good-to-have’ things are part of it. For instance, from a given spreadsheet, we cannot yet export data with conditions in Google Sheets.
Why export data with conditions in Google Sheets?
Bryan works as a data analyst at a major retail company that has outlets across the country. All the employees use Google Sheets for their spreadsheet needs. His job involves analyzing sales data, identify trends, skim the customer spending patterns and establish the likelihood of their upcoming purchases. Once he has the list ready, he will need to filter out those customers with a high probability of future purchases and send it to Kirk, from the customer services relations (CRM) team. Kirk would then reach out to each of those customers with attractive deals and offers, and persuade them to visit the store. Because those are the ones who can be easily moved with a simple marketing stimulus as opposed to the ones who are not planning to buy anytime soon.
Needless to say, the list of less probable customers is not of much use, and it’s better not to include them in the list. So, what can Bryan do? Every time he has a new list, he can filter it to retain only the high probable prospects and accordingly shares that filtered list to Kirk. It might look easy. But, what if there are a considerable number of categories and CRM representatives to deal with (let’s say about 50)? And, if he has to do that every day with the new list? Imagine the effort that Bryan has to put in to get out all the necessary information to the concerned parties. Not very easy! He would rather have this automated than manually deal with it every single day.
Solution: Automatically export the data with Sheetgo
There isn’t a native functionality available with which we can export data with conditions in Google Sheets. Therefore, we will need the help of the Sheetgo add-on, to accomplish this automation setup. This add-on highly boosts the connectivity of Google Sheets. It can automatically manage all the heavy-lifting that Bryan had to manually do (explained above). We can get it by clicking the button below.
Sheetgo add-on
Configuration: Steps to automatically export data with conditions in Google Sheets
For the purposes of demonstration, we will export data from the Customer Analysis file that Bryan uses, to the Sales Prospects file which Kirk will work on. For this to happen seamlessly, it is a pre-requisite that Kirk grants Bryan necessary edit access control to the file Sales Prospects.
1. Start Sheetgo and initiate a connection
Now that we have Sheetgo installed on our Google Sheets application, a new Google Sheets file will automatically open. This will be our Sales Prospects file where we want to export the filtered data coming from “Customer Analysis” to. To start the add-on, we navigate to the Menu Add-ons > Sheetgo > Start.
Doing so will open the Sheetgo interface on the screen as a sidebar, as shown in the image below. Click on the green + sign in the bottom right corner to create the connection with the Customer Analysis file.
2. Link with the source file to create the Export connection
Clicking the Select file(s) option leads us to the list of most recently updated files by default. If we don’t see the file we want, we can use the search module to lookup a file within Google Drive. For this demonstration, we select Customer Analysis file as Data Source, and the data will be exported from the first and only tab Sheet1.
We then arrive at the Settings screen. Here we can select the frequency of transfers in the Automatic update section. We enable this button and leave it to the default option, Daily because Bryan transfers the list every day. Also, in the future, if Bryan doesn’t need the automatic transfers anymore, he can turn them off in this section.
3. Add a filter condition for selective data export, and create a connection
Scroll down on the sidebar to reach the Filter by section. Enable this button to add a filter condition. The probability related field is on column F on the Customer Analysis file (please see the first snapshot). Bryan needs to send the list of all customers with probability greater than or equal to 0.6. Therefore we ensure it is Column F: Probability of next visit in the first drop-down, Number greater than or equal to in the second. The value in the text field will be, 0.6.
The Filter by section now shows Column F number greater than or equal to 0.6. Finally, to establish a link that can automatically export filtered data, we click on the SAVE CONNECTION button.
Once we do that, we will notice that in a few seconds Sheetgo creates a new connection whose status shows Last update: Just now. Awesome! We just did export data with conditions in Google Sheets! Here’s the snapshot of the file Sales Prospects.
We see that the data has appropriately flown in where all the probability values are greater than or equal to 0.6.
Automatic transfers
The data export keeps happening automatically and periodically once the appropriate connection is in place. Please note that we can change the frequency with which these data exports happen or even turn off the automatic transfers altogether. Shown below is how we navigate to the corresponding settings pane.
Click on the 3 dots next to the connection name (in this case “SG_Sheet1”) > edit (the pencil icon) > Automatic update.
Click on the green slider button to turn the automatic update off. Instead, we can keep the automatic update active, and change the scheduling frequency and execution time as required. Once done, click on the SAVE CHANGES button.
If you want to learn how to automatically import filtered data in Google Sheets, check out the following blog post.
