CodeNote.net

Get Twitter Followers/Following Count with Twitter API and Add to Google Sheets Sample Code - Google Apps Script (GAS) Edition

Tadashi Shigeoka ·  Sat, September 10, 2022

I’ll introduce Google Apps Script (GAS) sample code to get followers/following counts with Twitter API and add them to Google Sheets.

Twitter 分析

Background: Canceled SocialDog Paid Plan

I used SocialDog for about a month, but since I was only using some features, I canceled the paid plan.

For my use case, being able to track daily trends of Twitter account followers and following counts was sufficient, so I implemented my own process to add data to Google Sheets using Twitter API + GAS.

Prerequisites

Uses Twitter API GET /2/users/by/username/:username.

GAS Sample Code to Get Twitter Followers/Following Count and Add to Google Sheets

const TWITTER_USERNAME = PropertiesService.getScriptProperties().getProperty('TWITTER_USERNAME');
const TWITTER_API_BEARER_TOKEN = PropertiesService.getScriptProperties().getProperty('TWITTER_API_BEARER_TOKEN');
const GOOGLE_SHEET_ID = PropertiesService.getScriptProperties().getProperty('GOOGLE_SHEET_ID');

function getTwitterPublicMetrics(){
  const url = `https://api.twitter.com/2/users/by/username/${TWITTER_USERNAME}?user.fields=public_metrics`;
  const options = {
    method: 'get',
    headers: {
      authorization: `Bearer ${TWITTER_API_BEARER_TOKEN}`
    }
  };
  const response = JSON.parse(UrlFetchApp.fetch(url, options));

  const row = Object.values(response["data"]["public_metrics"]);
  const today = Utilities.formatDate(new Date(), 'JST', 'yyyy-MM-dd');
  row.unshift(today);

  const sheetName = 'シート1';
  const sheet = SpreadsheetApp.openById(GOOGLE_SHEET_ID).getSheetByName(sheetName);
  sheet.appendRow(row);
}

How to Use the GAS Sample Code

Create Spreadsheet

Add the following to row 1:

  • Column A: Date
  • Column B: Followers count
  • Column C: Following count
  • Column D: Total tweets
  • Column E: Listed count

Create GAS

From the spreadsheet menu [Extensions] - [Apps Script], create new and paste the sample code above.

Add GAS Trigger

Add a trigger to run once daily between 11 PM and midnight.

That’s all from the Gemba, where I used Twitter API + Google Apps Script to get followers/following counts and add them to Google Sheets.

Reference Information