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.

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 😉

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.

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.

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

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

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.

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.

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