Send daily automated tweets from a Google Sheet by using Google Scripts
Tags: codenewbie, tutorial, beginners, javascript
Published: 2019-11-24 23:46:35

---
canonical_url: https://inspiredwebdev.com/automate-tweets-from-google-sheet
---

> Check out my [blog](https://inspiredwebdev.com) for more articles or [Github](https://github.com/AlbertoMontalesi/The-complete-guide-to-modern-JavaScript) for my free-to-read JavaScript Ebook that covers all the new features from ES6 to 2019

&nbsp;

My girlfriend runs a [Vietnamese language blog](https://elingos.com) (which you should definitely check out, it's static site built with Gatsby that she designed and I built) and I wanted to help her be able to start a *Word of the day* project for twitter.

Having to go to Twitter every day just to post a new word is not ideal, repetitive tasks are the worst, they are boring and take up time that you could spend doing something else.

That's why I set up a simple Google Script to post a new tweet everyday from a Google Sheet. The whole process took me less than 2 hours with zero prior knowledge of how to use Google Scripts (which is very easy to use if you are familiar with JavaScript).

At the end of this tutorial you will know **how to create a script to get rows from a Google Sheet and send them as tweets**.

You can get the complete code for the script [here](https://github.com/AlbertoMontalesi/InspiredWebDev-Tutorials/blob/master/Google%20Script%20for%20twitter/google%20script.js)

Firstly, before we start we will need a twitter developer account, to do that follow this [link](https://developer.twitter.com/en/apps).

Once you apply it will take roughly a few hours to get approved.
What you want to do after that is create a new app and save both the Consumer API keys and the Access token & access token secret. Remember to not share them with anyone!

While your application is still pending approval from Twitter let's start creating our Google Sheet:

![Google sheet](https://raw.githubusercontent.com/AlbertoMontalesi/InspiredWebDevAssets/master/images/articles/google-sheet-twitter/google-sheet1.png)

It's a simple sheet with 4 column: 3 for the content and 1 for the date that will be used to post that tweet.

Now, inside of Google Sheet click on *Tools* > *Script editor* and a new window will open where you can write your Google Script.

Before we start writing our code, let's import the Twitter library that we'll need. To do that you need to click *Resources* > *Libraries* and paste this code inside of the *Add library* field: `MKvHYYdYA4G5JJHj7hxIcoh8V4oX7X1M_`.

![Google script library](https://raw.githubusercontent.com/AlbertoMontalesi/InspiredWebDevAssets/master/images/articles/google-sheet-twitter/google-sheeet2.png)

Before you save, rename the identifier of the library to `Twitter`, just so we can call methods on `Twitter.` instead of `Twitterlib.`, not necessary but just a personal preference.

&nbsp;

## Writing the Google Script

Now it's finally time to write our script which we can break down in two parts:

- parse the Google Sheet content, get the correct row and construct our tweet
- authenticate Twitter and tweet our post

My Google Sheet is structured so that I will be able to tweet a different *Word of the day* everyday.

The first thing I need to do is to parse the Google Sheet content and find the correct row to post today.

Let's start by getting our sheet like this:

```js
function sendDailyTweet() {
	var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
	var startRowNumber = 1;
	var endRowNumber = sheet.getLastRow();
}
```

Now what we want to do is to loop over all our rows and see if the date of that row corresponds to today.

Our function now will look like this:

```js
function sendDailyTweet() {
	var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
	var startRowNumber = 1;
	var endRowNumber = sheet.getLastRow();

	// variables to create our tweet
	var vietnameseWord;
	var englishWord;
	var sentenceExample;
	var identifier;
	
	for (var currentRowNumber = startRowNumber; currentRowNumber <= endRowNumber; currentRowNumber++) {
			var row = sheet.getRange(currentRowNumber + ":" + currentRowNumber)
			.getValues();
			// check that the fourth column (Date) is equal to today
			if (isToday(row[0][3])) {
				console.log(row);
				break;
			}
		}
}
```

What we are doing now is loop over each row and get the content of it with `sheet.getRange(currentRowNumber + ":" + currentRowNumber).getValues();`.

What we get is a an `Array` containing an `Array` of values, one per column.

Since the date was stored in the 4th column, I'm getting it like this: `row[0][3]` and passing it as an argument to the `isToday` function that we can define outside of our primary function as:

```js
function isToday(date) {
	var today = new Date();
	var dateFromRow = new Date(date);
	return dateFromRow.getDate() == today.getDate() &&
		dateFromRow.getMonth() == today.getMonth() &&
		dateFromRow.getFullYear() == today.getFullYear()
}
```

This function is very simple and will help us compare the current date with the one stored in that particular row. If they match it will return `true`

If you try now to run your code everything should work fine and you can see the output of the `console.log` by clicking on the menu *View* > *Stackdriver Logging*.

Now, replace that `console.log` with the following lines:

```js
vietnameseWord = row[0][0];
englishWord = row[0][1];
sentenceExample = row[0][2];
identifier = currentRowNumber -1;
```

We are simply assigning values to all the variables that we need based on the corresponding column.

Hopefully by now Twitter already approved your request so you can continue implementing the final part of this code.

After you create a new app on https://developer.twitter.com/ define a new `Object` inside of your `sendDailyTweet` function and replace my placeholders with your keys:

```js
var twitterKeys = {
    TWITTER_CONSUMER_KEY: "[your_key_here]",
    TWITTER_CONSUMER_SECRET: "[your_key_here]",
    TWITTER_ACCESS_TOKEN: "[your_key_here]",
    TWITTER_ACCESS_SECRET: "[your_key_here]",  
  }
```

Next, right under the `Object` let's store those properties like this:

```js
var props = PropertiesService.getScriptProperties();
props.setProperties(twitterKeys);
// we don't really need params for this example so we'll leave them empty
var params = new Array(0); 
var service = new Twitter.OAuth(props);
```

See this: `new Twitter.OAuth(props);`, we are calling `Twitter.` because we renamed the identifier of the library to `Twitter`, otherwise it would have been `Twitterlib.`.

Our final step will be to authenticate and finally send the tweet.

Right after our `for` loop, let's add this `if...else`:

```js
if (!service.hasAccess()) {
    console.log("Authentication Failed");
  } else {
		console.log("Authentication Successful");
	}
```

Try running your code now, if the credentials you passed in `props.setProperties(twitterKeys);` are correct, you shouldn't be having any problem.

Inside of the `else` statement we will construct our tweet and send it:

```js
var status = "Vietnamese Word #" + identifier + "\n\n" +
 vietnameseWord + " - " + englishWord + "\n\n" + "E.g: " + 
sentenceExample + "\n\n" +  
"Comment with your sentence. Learn more @ elingos.com" + 
"\n\n" + 
"#wordoftheday #languagelearning #learnvietnamese #vietnameselanguage #elingosvietnamese";
		try {
			var response = service.sendTweet(status, params);
			console.log(response);
		} catch (e) {
			console.log(e)
		}
```

We are using the variables we defined above to create the status for our tweet.

Inside of our `try...catch` we try to send our tweet with `service.sendTweet(status, params);`, otherwise we will see the error logged in our console.

Awesome, we are done with Google Script, the only thing missing here is a trigger to make our script run everyday.

To do that we don't have to write a single line of code, from the menu click *Edit* > *Current project's trigger* and a new tab will open.

Click the button *Add trigger* in the bottom corner

![google script trigger](https://raw.githubusercontent.com/AlbertoMontalesi/InspiredWebDevAssets/master/images/articles/google-sheet-twitter/google-sheet3.png)

These are the settings that I've chosen for my script:

- `sendDailyTweet` as the function to run
- time-driven as the event source
- day timer - 1am to 2am as the time frame

This script will run everyday and post a new tweet on the account twitter for [Elingos](https://twitter.com/Elingos1). If you like languages, real ones and not just programming ones, and you want to learn a new one, check out her blog at [Elingos.com](https://elingos.com).

You can get the complete code for the script [here](https://github.com/AlbertoMontalesi/InspiredWebDev-Tutorials/blob/master/Google%20Script%20for%20twitter/google%20script.js).

If you want to learn even an even more complex script, check out this great article on [Medium](https://medium.com/javascript-in-plain-english/i-made-a-twitter-bot-from-nothing-but-a-google-sheet-ef0ba6e1b194).

---

&nbsp;

Thank you very much for reading. Follow me on [DevTo](https://dev.to/albertomontalesi) or on my blog at [inspiredwebdev](https://inspiredwebdev.com/) for more.

<div class="promotional_banner">
<a href="http://a-fwd.to/5gUojI8" target="_blank">
<img alt="book banner" src="https://raw.githubusercontent.com/AlbertoMontalesi/The-complete-guide-to-modern-JavaScript/master/assets/banner.jpg" />
</a>
<span>Get my ebook on <a href="http://a-fwd.to/5gUojI8" target="_blank">Amazon</a> and <a href="https://leanpub.com/thecompleteguidetomodernjavascript2019" target="_blank">Leanpub</a></span>
</div>