How to Save Tweets in Google Sheets with Twitter Search [FREE] + Automate It

Have you ever found yourself in a situation where you needed to collect and save Tweets on Google Sheet using Twitter Live Search, but didn’t know where to start?

Certainly, I have.

As a marketer, I often need to collect a large number of tweets for my work, but I found that the process can be tedious and time-consuming. That’s when I discovered a free and easy solution to tweet archiver in Google Sheet using App Script function.

Twitter Search in Google Sheet

Don’t worry, you don’t have to code, I will give you an easy copy and paste code, where you just have to replace values.

As someone who uses Google Sheets regularly for data analysis, I was excited to learn that it’s possible to use Google App script to pull tweets from Twitter’s API and insert them directly into a sheet.

However, I quickly discovered that the process was not as straightforward as I had hoped, and many of the available solutions came with a price tag or required technical knowledge beyond my comfort level.

After much trial and error, I finally stumbled upon a free and easy method that even someone with minimal coding experience can implement

Let starts

Requirements :

  • An active Twitter account
  • Twitter API access
  • Google Sheets access
  • Learn how to copy and paste 😉

Table of Contents

How to Create and Apply for a Twitter Developer Account?

If you want to use Twitter’s API to scrape tweets and add them to a Google Sheet using a Google App script, the first step is to obtain the API key and secret credentials from Twitter’s Developer Account. These credentials will give you access to the Twitter API and allow you to make requests for the data you need.

How to Create Twitter Developer Account

Step 1: Have a Twitter Account

Create a Twitter account if you don’t already have one.

Step 2: Open Twitter Developer Website

Go to the Twitter Developer website and sign in with your Twitter account.

Step 3: Submit Application Details

Submit an application detailing your intended use of the Twitter API and the types of data you plan to access.

Step 4: Agree to Developer Policy and Verify Email

Agree to Twitter’s Developer Policy and verify your email address.

Step 5: Answer Additional Questions

Answer any additional questions about your intended use of the Twitter API and your technical skills.

Step 6: Receive API Credentials via Email

After providing all necessary information, Twitter will email you with your API key and secret credentials within 48 hours.

How to Create First App with Twitter Developer Account?

The steps to create a Twitter App are:

Step 1: Sign in to your Twitter Developer Account

Go to the Twitter Developer website and sign in to your account.

Step 2: Go to the Developer Dashboard

Once you are signed in, click on the “Developer Dashboard” button in the top right corner of the page.

Step 3: Create a New App

Click on the “Create App” button to create a new app.

Step 4: Fill in App Details

Fill in the details for your app, including the name, description, and website URL. You will also need to select the “App permissions” for the type of data you plan to access.

image

Since we’re just searching for twitter with a specific query, then reading access works, but if you’re going to create a Twitter or DM someone based on these details it’s suggested going with write access as well.

Select for Public App, it should work fine.

Step 5: Generate Client ID and Client Secret

Once you have filled in the app details, click on the “Keys and Tokens” tab to generate your Client ID & Secret tokens.

You will need to copy these credentials and keep them safe for later use.

For our case, we will just require Consumer Key and Consumer Secret Key

Getting Twitter ClientID and Secret ID

And we’re done.

Now the easiest part, copying 🙂

How to Add App Script Code in Google Sheets?

So now just create a fresh new Google Sheet, and then click on Extensions → App Script

Pasting Google Script in Google Sheets Extenstions

And then paste the below code

function getTweets() {
  var searchOperator = '@adityaarsharma'; // specify your search operator here
  var numTweets = 10; // specify the number of tweets to retrieve
  
  var tweets = searchTweets(searchOperator, numTweets);
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // clear current data in the sheet
  sheet.getDataRange().clear();
  
  // write headers to the sheet
  sheet.getRange(1, 1).setValue('Tweet ID');
  sheet.getRange(1, 2).setValue('Tweet Text');
  sheet.getRange(1, 3).setValue('Time of Host');
  
  // write tweets to the sheet
  for (var i = 0; i < tweets.length; i++) {
    sheet.getRange(i+2, 1).setValue(tweets[i].id_str);
    sheet.getRange(i+2, 2).setValue(tweets[i].text);
    sheet.getRange(i+2, 3).setValue(formatDate(tweets[i].created_at));
  }
}

function searchTweets(searchOperator, numTweets) {
  var tweets = [];
  
  var url = 'https://api.twitter.com/1.1/search/tweets.json?q=' + searchOperator + '&result_type=recent&count=' + numTweets;
  var response = UrlFetchApp.fetch(url, {
    headers: {
      'Authorization': 'Bearer ' + getBearerToken()
    }
  });
  
  var data = JSON.parse(response.getContentText());
  for (var i = 0; i < data.statuses.length; i++) {
    var tweet = data.statuses[i];
    tweets.push(tweet);
  }
  
  return tweets;
}

function getBearerToken() {
  var consumerKey = 'ADD-YOUR-CONSUMER-KEY';
  var consumerSecret = 'ADD-YOUR-CONSUMER-SECRET';
  
  var encodedConsumerKey = encodeURIComponent(consumerKey);
  var encodedConsumerSecret = encodeURIComponent(consumerSecret);
  var combined = encodedConsumerKey + ':' + encodedConsumerSecret;
  var base64Encoded = Utilities.base64Encode(combined);
  
  var url = 'https://api.twitter.com/oauth2/token';
  var response = UrlFetchApp.fetch(url, {
    method: 'post',
    headers: {
      'Authorization': 'Basic ' + base64Encoded,
      'Content-Type': 'application/x-www-form-urlencoded;charset=UTF-8'
    },
    payload: 'grant_type=client_credentials'
  });
  
  var data = JSON.parse(response.getContentText());
  var token = data.access_token;
  
  return token;
}

function formatDate(dateStr) {
  var date = new Date(Date.parse(dateStr));
  return Utilities.formatDate(date, 'GMT', 'yyyy-MM-dd\'T\'HH:mm:ss\'Z\'');
}

To make the Twitter search and Google Sheet integration work, you need to add your Twitter App’s API keys in the script. Replace “ADD-YOUR-CONSUMER-SECRET” and “ADD-YOUR-CONSUMER-KEY” with your app’s keys in the script.

In the second line of the script, add your advanced search operator for Twitter.

For example, if you want to search for tweets from a specific account, you can add their username like this: “@adityaarsharma”.

If you have any questions or need help with your search operator, feel free to connect with me on Twitter.

After adding your API keys and search operator, simply run the script from your Google Sheet. The script will create three columns in the sheet: Tweet ID, Tweet Text, and Time of Host.

If you need any additional data, don’t hesitate to reach out to me.

By automating the process with a trigger, you can save time and effort in collecting and analyzing Twitter data.



I share my Learnings & Case studies via email.
Subscribe to Stay Updated

How to Schedule Tweets Collection at Regular Interval?

Did you find adding tweets to a Google Sheet using Twitter Search and Google App script helpful?

Great! Let’s take it a step further and automate the process.

Automate Google Script with Trigger

Rather than manually running the script every time you want to update the sheet with new tweets, you can set up a trigger in Google App script to automatically run the script at set intervals.

Here’s how:

  1. In the Google App script dashboard, click on “Triggers” and then “Add Trigger”.
  2. Choose the function you created earlier, in our case “getTweets”.
  3. Set the event source to “Time-driven” and select the interval you want the script to run at.
  4. Click “Save”.

Now, at the selected time interval, the Google Sheet will automatically be updated with new tweets based on the search operator you provided. This will save you time and effort in data collection and analysis.

If you need any help customizing the script or have any questions, feel free to reach out to me via Twitter or email. I’d be happy to assist you.

Don’t forget to share how you’ve made use of this workflow and saved time in the comments. And stay tuned for more amazing tips and tricks on using Google Sheets!

Frequently Asked Questions

Do I need to know how to code to use Google App script for adding tweets to Google Sheets?

While some coding knowledge can be helpful, it’s not necessary to know how to code to use Google App script for adding tweets to Google Sheets. With a bit of patience and following the instructions carefully, anyone can implement this solution.

Can I use this solution to retrieve tweets from multiple Twitter accounts at once?

No, this solution is designed to retrieve tweets based on a specific search operator. To retrieve tweets from multiple accounts, you would need to modify the search operator to include the usernames of the accounts you want to retrieve tweets from.

Can I retrieve tweets from a specific date range using this solution?

Yes, you can modify the search operator to include a date range. For example, if you want to retrieve tweets between January 1, 2022 and January 31, 2022, you can add “since:2022-01-01 until:2022-01-31” to your search operator.

Is there a limit to the number of tweets I can retrieve using this solution?

Yes, there is a limit to the number of tweets you can retrieve using the Twitter API. The limit varies based on the type of API endpoint you are using and the number of requests you make within a given time period. However, for most users, the limit is unlikely to be reached.

Can I customize the script to include additional data fields in my Google Sheet?

Yes, you can customize the script to include additional data fields in your Google Sheet. However, this would require modifying the script to retrieve and format the additional data. If you need help with this, feel free to connect with me on Twitter or email.

Is it safe to use Google App script for retrieving Twitter data?

Yes, it’s safe to use Google App script for retrieving Twitter data. However, it’s important to follow Twitter’s API guidelines and not exceed the rate limits or use the data for prohibited purposes.

Aditya R Sharma

Hello! Currently, I am working as Chief Marketing Officer at POSIMYTH Innovations, with a deep love for WordPress. I love solving problem with AI, Automations & Critical planning. In my free time, I write blogs to share all his learnings with the internet.

Content Writing for SaaS & WordPress Business
Tired of Low Traffic & see your Blog Fail?

Let me help you with content writing & SEO strategy for your product that makes sales.

After raising multiple domains to 1M+ organic click, I am helping Business owners like you harness the real power of SEO.

Explore Further