Get daily trending topics from Google Trends into Google Sheets every day without using code 🚀
Check out the video tutorial 🎥
2. Video Transcription
**This is detailed step-by-step tutorial, so the transcription will only make sense in context with the video. I have also added the transcript onto the video.**
Hi everyone. So we’re going to be doing something a little different on Keywords in Sheets today. No scripts, templates; a completely no-code solution. To get Google Trends data into Sheets, it’s going to be a manual approach where you would have to click a button every day but I’m also going to show you how you can automate it everyday in Sheets through Zapier, it’s pretty simple.
We do need a few tools to do it. So the first one being an automation bot called Axiom. It’s great. It’s probably one of the best ones I’ve found for the price, and you get 30 minutes of runtime for free – our app is going to be around one to two minutes of runtime, potentially less. And also, we need Zapier for the the automation. I think you get 100 tasks per month on the free plan, so that should be more than enough to run this every day.
2. Setting up a task and extracting data in Axiom.Ai
We’ll be looking at the Google Trends Daily Trends Report. Mine’s filtered by the UK but obviously you can filter by whichever country you’d like. Once you’ve signed up to Axiom, you’ll be able to get access to the Chrome extension.
We can create a new automation. We’ll call this G Trends. And we want to build our own Axiom, step-by-step. So the first step is that we want to get ‘Data From A Webpage’. So it automatically pulls in the URL, which we’re on. And what we need to do is select the data that we’d like from the page.
So just click ‘Select Data’, and you’ll see columns appear, and this is where we can start adding our data. So on the first one, we want to get the ID. If you select one element, and then select another, it starts to get the pattern.
Add another column. This time, we want the actual trend label.
Next, we probably want the publisher label. So again, do a similar thing.
And I think next we probably want the publisher URL. So in this case, what we have select is the publisher, the title of the main trend, again, just like one or two, but in this case, we want to change from ‘text’ to ‘link’ and then that’ll get the publish link.
And then finally, I think it would be good to get the volume of searches, so if we add one final column and move along. Column E will be sort of 50k searches. So that looks fine for our data. So that’s all good. It’s currently limited to 20 results. It does depend on the day and the time of how many trends appear. So I think that one, the limit will be fine, you can remove that limit if you’d like.
3. Manipulating your data in Axion.Ai
When you select the trend label, it actually appends the trend label with ‘share’. And that’s because when you actually click on the element, another element appears and the title has a share button. So we do need to manipulate that data somewhat but you can do that pretty easily in Axiom.
I’m just gonna find the Axiom task that allows us to do that. So we want to ‘Replace Text’. We want to replace ‘share’ and we want to replace that with a space. And that’s that step complete.
4. Writing your data to a Google Sheet
On the next step, we want to write the data to a Google Sheet. So we need our Sheets URL (I’ve already built one). So all you need to do is just copy your Google Sheets URL.
Within Axiom you connect your Google Sheets account, so just passing the URL should be absolutely fine. We’ll go back to Trends, add our Sheets URL, and what we want to do is add the Sheets name, which is ‘Trends; and we want the data to be the ‘Replace Text’; the one that removes ‘Share’.
And there’s a couple of additional things. So you can clear the data every time or you can append to the existing data in Sheets. So if it runs every day, you can start to see some daily data appended if you’d like, but mine’s just gonna clear the data in Google Sheets before writing every day.
5. Manually running your Axiom.Ai task
And that’s it all set up. So we can manually run this. And when you run it, you’ll be able to see it being run – you can actually remove this by selecting ‘Headless’ within axiom itself but right now we’ll see it run just to make sure it runs successfully.
Great. So that’s all been completed – it’s ran absolutely fine. So if we head back to the Google Sheet, we should see that data in there. Great. So now we’ve got the ID, we’ve got the Trend, we’ve got the publisher, the publisher URL, and we’ve got the share count in there.
6. Scheduling Axiom.Ai runs every day using Zapier
As it stands, we would have to run this within Axiom everyday. Again, it’s pretty simple. It’s a Chrome extension. So you could just go into your Axiom, and click run every day if you wanted to, but we want to build a bit more of an automated solution.
So Axiom has a Zap in Beta, so you do need to get in touch with the Co-Founder via their support. If you head to support, you’ll be presented with a screen like this; you don’t need to share a link to your screen recording but just ask for access to the Beta within your free plan and you’ll be sent it pretty much straight away. They’re very, very quick and very responsive.
Once you’ve done that head over to zap. The trigger is going to be a scheduler and we want to trigger this every day. Time of day, I guess, doesn’t really matter – we’ll select noon – and we want our action to be an Axiom. We want to run an Axiom -> select your account -> and on the Axiom name, all you need to select is the name that you’ve given your Axiom -> Click Continue – > and that’s it all set up.
As soon as that’s set up, it will run the axiom at Noon every day and everything will be in your allowance. And it’s as simple as that.
I hope you enjoyed it! If you’ve got any issues or questions, just leave them in the comments below and I’ll get back to you straight away or by all means get in touch with me on Twitter or by email
Thanks for stopping by 👋
I’m Andrew Charlton, the Google Sheets nerd behind Keywords in Sheets. 🤓
Questions? Get in touch with me on social or comment below 👇