To simplify the original post and to make steps a little simpler I'm moving the details on the Apps Script that runs behind the Sheets to Tweets Twitter Scheduler to this post.
The script relies on one external LIbrary, Twitter Lib. Twitter Lib is a Google Apps Script maintained by Bradley Momberg. I originally started by writing all the calls to Twitter myself but I ran into a few bugs and decided to leverage something that already works instead of trying to recreate the wheel. It's always best to use someone's code that is maintained if you can and then extend it if needed. I ended up doing some very minor extensions to Twitter Lib which I talk about below.
The project has 4 script files included.
This file contains code to handle interaction with the Google Sheets UI.
This file contains utility methods used in the project.
This file contains overloads to the Twitter lib sendTweet and uploadMedia methods. I modified the behavior slightly in order to handle error messaging in the Sheet a little better.
This file contains the main logic of the script. There are 2 main methods in this script that I'll cover here.
This method setups up the Twitter API call based on the settings in the Settings sheet using Twitter Lib and returns a reference to the service.
This is the method that gets called when the scheduler runs. Here is the basic logic:
- Open the Twitter Sheet
- Get all tweets between Now and Now plus the time set for Schedule Interval (i.e. 15 Minutes)
- If multiple Tweets are being sent in this cycle, and this isn't the first Tweet in the cycle, delay sending Tweet for 1 min and update Status to Pending.
- Upload any Images for the Tweet and store the Media IDs.
- Upload the Status Update using any Media IDs created and store the Tweet ID and any Media IDs in the sheet. Update sheet with status with Posted or Failed and report the time posted or error.