Filtro do Google Sheets de outra planilha

Google Sheets application is developed with one central aim – facilitate collaboration and easy information exchange. It is a huge stride forward from the erstwhile single-user off-line mode to multi-user online mode of handling data. Despite the advancements, we still lack certain features. For example, we can access complete files from other users, but we cannot use Google Sheets filter from another sheet using a variable date.

Why would Google Sheets filter from another sheet?

Let us find out based on an example. Dominic is the owner of a blog, where he writes free technical advice on office tools and productivity. He also offers paid courses through e-books and video tutorials. It is configured such that the information of the users who subscribe to his blog, is stored in a dedicated spreadsheet. Ever since his website has come to prominence, the user subscription rate has surged.

He has an employee, Tina, whose job is to extract user information and send them targeted content and course offerings. Since she does the job almost every day, she would rather have the latest subscriptions on her screen as opposed to the thousands of older ones.

Solution: Automatically import using the Sheetgo add-on

For Tina to be able to access the subscription spreadsheet, Dominic will have to grant her necessary read privileges on the file. For the purposes of demonstration, we will import queried data from the master file Subscribed User Information into the file Todays Subscription List.

1. Start Sheetgo and initiate an export link connection

If not already installed, we can get the Sheetgo add-on for Google Sheets by clicking on the button below:

Once installed, a new Google Sheets will automatically open. Give the file an appropriate name, in this case, Todays Subscription List. 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. Click on the green + sign to create a connection.

2. Link destination file with the source sheet that we need to import the data from

Clicando no Selecione o(s) arquivo(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 example, we select Subscribed User Information file for data source, and then for source sheet tab choose Usuários.

We then arrive at the Configurações screen. Here we can rename the ‘Connection name’ to “SG_Users”. We can also select the frequency of updates in the Atualização automática section. We enable this button and leave it to the default option, Diariamente because Tina needs to import from Subscribed User Information file every day.

3. Add the filter condition to import data for today’s date, and create a connection

To do this, we scroll down a bit on the sidebar until we see the Filtrar por section. By default, it the button is turned off. Enable this button and add a condition. From the first image on this post, we realize that the ‘Subscribed On’ is in column F. Since we need to create a filter based on this field, we ensure it is Column F: Subscribed On in the first drop-down. The second and third drop-downs are Date is e Today respectively, because we are looking for today’s information.

Finally, to establish the link that can automatically import filtered data in Google Sheets using a variable date, we click the SAVE CONNECTION botão.

Congratulations! We’ve just created a connection with the Sheetgo add-on that we can use to import filtered data and allow Google Sheets filter from another sheet. Here’s how the sheet looks after the import.

Tina takes a day off

One fine day, Tina did not keep well, and she did not come to work. When she comes into office the next day, the connection we created above with Date is today condition doesn’t help her. Because it will not give her yesterday’s subscription list which she missed due to illness. So, how does she retrieve that information? Well, it is actually very simple.

Let’s assume that Tina was away from work on 20-Dec-2018. So, she will need data from 20th and 21st of Dec 2018. She just has to open the Sheetgo add-on while on the Todays Subscription List file and add an extra filter condition “Date is” “Yesterday”. 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_Users”) > editar (the pencil icon) > Filter by > editar.

Similarly, Tina could also make the best use of Date is before condition to suit her needs as and when required.

Atualizações automáticas

While we did import filtered data in Google Sheets, this keeps on happening automatically and periodically once the appropriate connection is in place. Please note that we can change the frequency with which these data imports happen or even turn off the automatic update 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_Users”) > edit (the pencil icon) > Automatic update > edit.

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 SALVAR CONEXÃO button again.

This is how you filter Google Sheets from another sheet, using a variable date. Learn in esta postagem do blog how to filter by color in Google Sheets.