Building a Google Sheets Twitter Bot with Pipedream

Building a Google Sheets Twitter Bot with Pipedream

This is something that's been kicking around my head for a week or so and today I thought I'd try it. It ended up taking about 20 minutes total and 10 lines of code, of which 5 are a function I copied and pasted. While what I built is kind of trivial, I'm blown away by how much was done by built-in functions with Pipedream and how little work I had to do myself. In fact, most of my time was spent in setting stuff up outside of Pipedream itself. Alright, so what did I build?

I've got a kind of fascination (ok, a problem) with building Twitter bots, especially those that share random content. Earlier this month I created a Twitter bot that uses Wikia APIs to scrape GI Joe content. One problem with my "random bots" is that, well, they're random, and I don't have full control over the data itself. It's possible there's something on the GI Joe wiki that I'm not aware of. And since it's a wiki, even if I check every single page now, in the future something may be added that I don't want my bot to pick up.

So I thought - what if the random bot was tied to content that I had full control over? Also - what if the content was in an easily editable form, something a non-developer could use. It occurred to me that Google Sheets could be great for this. With that in mind, built moonpicbot. This is a bot that shares pictures of the moon driven by public domain NASA images.

While NASA may have an API (I'm pretty sure they do), I instead built a Google Sheet where I manually selected some pictures I thought were nice.

Google Sheet screenshot

I then registered my bot which is mainly painless now that I've done it multiple, multiple times. ;)

With my data in place, I designed the following workflow in Pipedream:

  1. Use a CRON trigger to schedule the tweets. Currently mine's once every two hours.
  2. Connect and read my Google Sheet.
  3. Select a random row.
  4. Upload the image.
  5. Tweet the text and the image.

Alright, here's comes the cool part. Pipedream handled steps 1, 2, 4, and 5. I've shown their CRON trigger before, but here's the Google Sheet action. I connected it to my app and pasted in the sheet ID:

Google Sheet step

Make note of the range. My sheet uses two columns so my range goes from A2 in one corner (A1 is the header) to B999 in the other. That means if I ever have one thousand rows I'll need to edit the range. That will take about 5 seconds so I'm not concerned, and again, since I'm manually controlling the data for this bot, I'll know.

Step 3 is where I wrote code:

async (event, steps) => {
	function getRandomIntInclusive(min, max) {
		min = Math.ceil(min);
		max = Math.floor(max);
		return Math.floor(Math.random() * (max - min + 1)) + min; //The maximum is inclusive and the minimum is inclusive 
	}

	let selectedRow = steps.get_values.$return_value.values[getRandomIntInclusive(0,steps.get_values.$return_value.values.length-1)];
	this.image = 'https://' +selectedRow[0];
	this.text = selectedRow[1];
	// hard coded for now
	this.mimetype = 'image/jpeg';
}

That's a bit over ten lines of which about half is a function to handle getting the random value. I slightly modify the image to include https (the NASA site didn't have this) and hard code a mimetype.

And that's it. I'm done. I added a upload_media_to_twitter step and then a post_tweet step. I could share this sheet now with a non-technical user and they could control the bot as they see fit. You can see, and fork, the entire workflow here: https://pipedream.com/@raymondcamden/random-moon-p_WxC9jR/edit

Header photo by Sanni Sahil on Unsplash

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Dylan Sather posted on 4/28/2020 at 6:25 AM

“My sheet uses two columns so my range goes from A2 in one corner (A1 is the header) to B999 in the other. That means if I ever have one thousand rows I'll need to edit the range”

I _think_ simply A:B works, and extends the range to future columns. On mobile so can’t test right now.

Comment 2 by Dylan Sather posted on 4/28/2020 at 6:36 AM

Also, you could pass the array of rows returned by the Sheets action to the “Get Random Element from Array” action and avoid the getRandomIntInclusive function: https://pipedream.com/@dyla...

Comment 3 (In reply to #2) by Raymond Camden posted on 4/28/2020 at 6:39 AM

Ah, I didn't know such a thing existed. This is why we need a good way to browse all the shared actions. :)

Comment 4 (In reply to #1) by Raymond Camden posted on 4/28/2020 at 6:41 AM

But wouldn't A:B also return row one? I could get rid of the header, but I was imaging this being used by a non-technical person. I could also delete row one in code before selecting the random item.

Comment 5 (In reply to #4) by Dylan Sather posted on 4/28/2020 at 3:58 PM

Good call. Check out how I modified the Get Values from Sheets action to include a "Include Header Row" param: https://pipedream.com/@dylb... .

I'm including this in the code for the original action so you'll see this next time you use it (just getting a code review on that change).

Comment 6 (In reply to #3) by Dylan Sather posted on 4/28/2020 at 3:59 PM

I hear you. Event sources are the priority now because they're higher value for so many users, but that'll be coming.

Comment 7 (In reply to #5) by Raymond Camden posted on 4/28/2020 at 4:06 PM

A few thoughts:

I think the docs on this need to be clearer. If I ask for A1, and I have ignore header row, does that mean A1 is really A2? In general I'd be afraid of using this as I wouldn't be sure what it does to 1-N indexes. Maybe it only makes sense if you have an option to get all? If there was a get all, I'd imagine the range thing would be disabled, and then this option would be useful. And speaking of - I see range is optional. I didn't even notice that before. If left blank, is everything returned? That may be obvious, but if so, I'd make it clearer.

Finally - there should be a way for me, when looking at a workflow, to check and see which steps have been updated. That way I can optionally choose to "swap in" the new version (and maybe rollback if things break).

Comment 8 (In reply to #7) by Dylan Sather posted on 4/28/2020 at 4:26 PM

Would "Include First Row" be more clear? The use case for removing the header row is so common I do think it makes sense (as an optional param that defaults to true). This manipulates the data _after_ Sheets returns it, so if you include A1:A10, and remove the header row, you'd still fetch the values in A1:10, but the action would return the rows A2:A10. We can clarify that language if that helps?

Range is required, unless you're seeing something I'm not?

https://uploads.disquscdn.c...

I hear you on the action update use case. I realized we didn't have a public issue for that so feel free to +1 here: https://github.com/Pipedrea...

Comment 9 (In reply to #8) by Raymond Camden posted on 4/28/2020 at 4:44 PM

Um.... honestly I don't think I get it. If I'm specifying a range, why wouldn't I just use A2 if I don't want the headers? In other words, why would I use A1 if my intent is to not include the header? (And sorry, I misread the UI, range is required.)

I absolutely agree that removing the headers is a common use case, but if range is required, I also don't see why I'd ever include the header in that case. The only time I would be when using what you had suggested as a way to avoid specifying an end range.

Comment 10 (In reply to #9) by Dylan Sather posted on 4/28/2020 at 5:22 PM

Yeah, I was trying to avoid users having to hardcode the start and end of the range. We see this use case a lot: I want all records in a given column (without having to hardcode the end number of the range), but usually don't want the header row. This combination of options (A:B, Include Header Row = false) is just one way to approach the problem.

Comment 11 (In reply to #10) by Raymond Camden posted on 4/28/2020 at 5:28 PM

So if I do A1 as a range and exclude headers, is my first row the second row? ie result[0] will be the first row of data? If I do A2 and exclude headers, what's the first row - the same? Ie it basically says "you didn't ask for the header but asked me to exclude it but I don't need to do anything" ?

Comment 12 (In reply to #11) by Dylan Sather posted on 4/28/2020 at 5:43 PM

Setting that param to false just removes the first element of the values property returned by Google Sheets:


if (includeHeader === false) {
// Remove the first row
response.data.values.shift()
}

If you just specify A1 and set includeHeader to false, you'll get no results: you asked for one row, and you also asked to remove the first row. Same with just including A2 and setting includeHeader to false: no data. That's why I think it makes sense to change the param to "Include First Row", since you're not always asking for a range that starts at A1 (in that case you probably would have never used the Include First Row param to begin with, though).

I empathize with the confusion, though. I'm letting all this marinate to see if there's a way to clarify in description / inline docs.

Comment 13 (In reply to #12) by Raymond Camden posted on 4/28/2020 at 5:54 PM

First off, an apology, when I said A1 and A2, I was being lazy, I meant using A1 or A2 as a starting point, so assume A1-A10 or some such. Let me rephrase then:

If I ask for A1 to A10, what happens when the setting, whatever it is, is on or off.
If I ask for A2 to A10, what happens when the setting is on or off?

I honestly think I would _not_ include this setting unless you specifically support an option to 'get all'. I think as soon as you ask for a range, you are being very specific on what you want, and this other setting then is just going to be confusing.

Comment 14 (In reply to #13) by Dylan Sather posted on 4/28/2020 at 5:56 PM

Sure, that's good feedback.