Under the Hood of Sheet Tweetr

In this post we’ll take a look at the guts of Sheet Tweetr to see how everything works. This is a deeper dive than the intro article, API Sample App: Sheet Tweetr. The source code for Sheet Tweetr is available on GitHub.  

Sheet Tweetr is a simple application that sends tweets from Smartsheet and serves as a sample app for working with the Smartsheet JavaScript SDK. Sheet Tweetr reads a sheet, checks each row to see when the tweet is scheduled to go out, and posts the tweet when the time is right. The tweet can go out multiple times depending on the frequency set by the user.

Smartsheet and Sheet Tweetr

Sheet Tweetr makes use of Smartsheet’s API to retrieve the list of tweets and make updates to a tweet's row after it’s posted. Smartsheet plays another important role in Sheet Tweetr—serving as the interface used to input and store tweets. 

To set up Sheet Tweetr, a user needs a Smartsheet Access Token and the Smartsheet JavaScript SDK. If you’re new to the Smartsheet API, then the article ‘Getting started with the Smartsheet API’ is a good place to start. When you have questions, the Smartsheet API FAQ is a great resource. 

Following the Data Through Sheet Tweetr → getListOfItems()

The entry point into Sheet Tweetr’s code is the getListOfItems() function, which invokes the getSheet() method from Smartsheet’s JavaScript SDK. After receiving the sheet’s data in response, the following steps take place:

• Loop through each row in the sheet.

• During every iteration, each row’s respective data is used to create a scheduledItem object.

• The details of each scheduledItem are checked to determine whether it’s valid (meaning ready to be tweeted).

• If valid, the scheduledItem is pushed into an array named itemsWithValidDates.

• After each row has been checked, itemsWithValidDates is passed to postTweet().

    function getListOfItems() {
      smartsheetClient.sheets.getSheet(options, (error, data) => {
        if (error) {
          console.log(error);
          return error;
        }
        for (let i = 0; i < data.rows.length; i++) {
          let row = data.rows[i];
          let simpleRow = createSimpleRow(row);
          let scheduledItem = {
            tweetText: simpleRow[COLUMN_MAP.tweetTextCID],
            tweetImage: simpleRow[COLUMN_MAP.tweetImageCID],
            tweetStartDate: Date.parse(simpleRow[COLUMN_MAP.tweetStartCID]),
            tweetEndDate: Date.parse(simpleRow[COLUMN_MAP.tweetEndCID]),
            lastRan: simpleRow[COLUMN_MAP.tweetLastRanCID],
            freq: simpleRow[COLUMN_MAP.tweetFreqCID],
            freqType: simpleRow[COLUMN_MAP.tweetFreqTypeCID],
            tweetTime: simpleRow[COLUMN_MAP.tweetTimeCID],
            rowId: row.id,
          };
          let result = validateItem(scheduledItem);
  
          if (result === true) {
            itemsWithValidDates.push(scheduledItem);
            continue;
          }
        }
        postTweet(itemsWithValidDates);
      });
    }

  In the code above, you'll see the createSimpleRow() function. It grabs the column id and value from each cell in a row—making the values easier to reference when manually assembling each row into a scheduledItem. The scheduledItem is passed to validateItem(), a function responsible for examining each tweet to determine whether it’s valid, and should be sent to Twitter. Valid tweets are then pushed into an array which is to postTweet() after Sheet Tweetr has looped through each row.

Determining Which Tweets to Post → validateItem() & examineCriteria()

When the validateItem() function is invoked, the scheduledItem’s properties are passed to examineCriteria(). If a flag is returned that matches firstPost, specificPostTime, or validPost, then validateItem() returns true—telling the getListOfItems() function that respective tweet is valid.

    function validateItem(scheduledItem) {
        scheduledItem.tweetTime === undefined ? scheduledItem.tweetTime = 
        "12:00 AM" : scheduledItem.tweetTime;
        const isValidItem = examineCriteria(
          scheduledItem.tweetStartDate,
          scheduledItem.tweetEndDate,
          scheduledItem.tweetTime,
          scheduledItem.lastRan,
          scheduledItem.freq,
          scheduledItem.freqType,
          Date.now()
        );
        if (
          isValidItem.firstPost ||
          isValidItem.validPost
        ) {
          return true;
        }
    }

It may not be apparent at first what examineCriteria() is doing, but it’s actually quite simple at heart. The code below contains the logic that determines whether a tweet is ready to be posted.

      function examineCriteria(
        tweetStartDate,
        tweetEndDate,
        tweetTime,
        lastRan,
        freq,
        freqType,
        comparedTo
      ) {
        // USE CASE 1: date does not fall within given range
        if (tweetStartDate >= comparedTo || tweetEndDate <= comparedTo) {
          console.log('Invalid date range');
          return { invalidDate: true };
        }
        // USE CASE 2: scheduled item has valid dates, but has not been posted yet
        if (lastRan === undefined) {
          let postTime = specificTime(tweetTime, tweetStartDate).getTime();
          const oneHour = 3.6e6;
          const oneHourBefore = comparedTo - oneHour;
          const oneHourAfter = comparedTo + oneHour;
          if (postTime <= oneHourAfter && postTime >= oneHourBefore) {
            console.log('close enough...');
            return { firstPost: true };
          }
        }
        // USE CASE 3: the tweet has valid dates and should go out again
        let nextTweetDate = frequencyCheck(lastRan, freq, freqType);
        if (nextTweetDate.getTime() < comparedTo) {
          return { validPost: true };
        }
        return {};
      }

Let's take a deeper look at each use case.
Use Case 1.) The tweet’s comparedTo date falls outside the start and end date on the sheet.

 if (tweetStartDate >= comparedTo || tweetEndDate <= comparedTo)

First condition: a start date greater than comparedTo means it’s too early to post the tweet. The current date hasn’t reached the tweet’s start date yet.
Second condition: an end date less than comparedTo means it’s too late to post the tweet. The current date has already passed the tweet’s end date.

Use Case 2.) The tweet has not been posted yet.

    if (lastRan === undefined)

This condition grabs all first time tweets.

Use Case 3.) The tweet was already posted and should be again if enough time has passed.

    let nextTweetDate = frequencyCheck(lastRan, freq, freqType);

The frequencyCheck() function takes lastRan, the time that tweet was posted last, and adds the frequency—returning the future time the tweet can be posted.

    if (nextTweetDate.getTime() < comparedTo)

If nextTweetDate is less than comparedTo, then enough time has passed for the tweet to go out again and the tweet is valid.

Sending Tweets to Twitter → postTweet() & tweet()

The postTweet() function takes in validItems, an array of tweets. If the array is empty, then there are no valid tweets and Sheet Tweetr can terminate. If the array has entries, then shift() returns the first entry and removes it from the array before invoking tweetTheRow().

      function postTweet(validItems) {
        if (!validItems.length) {
          console.log('no valid tweets available');
          return cb();
        }
        if (validItems.length > 0) {
          let nextItem = validItems.shift();
          tweetTheRow(nextItem);
        }
      }

Sheet Tweetr makes use of a well-documented node module named ‘Twit’ that integrates Twitter and Node.js. The first step is creating a Twit instance, the entry point to Twit’s methods, by providing properties that are used to format the request being sent to the Twitter API.

    const Twitter = new twit({
        consumer_key: ctx.secrets.TWITTER_CONSUMER_KEY,
        consumer_secret: ctx.secrets.TWITTER_CONSUMER_SECRET,
        access_token: ctx.secrets.TWITTER_ACCESS_TOKEN,
        access_token_secret: ctx.secrets.TWITTER_ACCESS_SECRET,
        timeout_ms: 60 * 1000, // optional HTTP request timeout.
    });

The use of Twit is pretty straightforward. Most methods take parameters describing the data to send to Twitter and a callback that gets invoked when the result (success or fail) is obtained. Each time a method from Twit is used we’ll need to provide a callback that gets invoked with the results of the API call.

Every tweet is sent using this function:

     function tweet(status, rowId) {
        Twitter.post('statuses/update', status, (err, data, response) => {
          if (err) {
            console.log(`error ${err}`);
            postTweet(itemsWithValidDates);
            return err;
          }
          console.log('successfully tweeted');
          updateSheetRow(rowId);
          return cb();
        });
      }

The tweet() function takes in two arguments: status, the tweet’s text and/or image, and the rowId. If a tweet is successfully posted, then Sheet Tweetr updates that tweet’s row in the sheet with updateSheetRow() and the application terminates. However, if an error is thrown then the postTweet() function is called again.

Updating the Tweet’s Row in Smartsheet → updateSheetRow()

The updateSheetRow() method from the Smartsheet’s SDK allows Sheet Tweetr to update specific rows as needed. In the sheet connected to Sheet Tweetr, the column named LastRan is used to save a timestamp of the specific time a tweet was posted. This timestamp is important—serving as a point of reference to check the frequency against. LastRan must be updated every time a tweet goes out.

     function updateSheetRow(rowId) {
        const rowUpdate = [
          {
            id: rowId,
            cells: [
              {
                columnId: COLUMN_MAP['tweetLastRanCID'],
                value: Date.now(),
              },
            ],
          },
        ];
        const options = {
          body: rowUpdate,
          sheetId: ctx.secrets.SMARTSHEET_SHEET_ID,
        };
        smartsheetClient.sheets.updateRow(options, (error, data) => {
          if (error) {
            console.log(error);
            return error;
          }
          console.log('successfully updated');
        });
      }

Passing an Image from Smartsheet to Twitter

Tweets that contain an image need to be handled differently than tweets that only contain text.

    function tweetWithImg(text, image64, rowId) {
        Twitter.post(
          'media/upload',
          { media_data: image64 },
          (err, media, response) => {
            if (err) {
              console.log(`error ${err}`);
              return err;
            }
            const status = {
              status: text,
              media_ids: media.media_id_string,
            };
            tweet(status, rowId);
          }
        );
      }

The main differences between tweetWithImg() and tweet() are a different endpoint (to upload media to Twitter), an extra property (media_ids) in the status object, and a new argument called image64. The image64 argument needs to be a base64 encoded image.

Let’s take a look at the getImgData() function. The image is immediately base64 encoded once it’s returned from Smartsheet. This base64 string can be used to upload the image to Twitter.

Note: The Smartsheet API call below looks a little different than the others. That’s because at the time of this writing there is not a method in the Smartsheet JavaScript SDK to download images.

    function getImgData(imageId, imageReturn) {
        const options = {
          method: 'POST',
          url: 'https://api.smartsheet.com/2.0/imageurls',
          headers: {
            authorization: 'Bearer ' + ctx.secrets.SMARTSHEET_ACCESS_TOKEN,
          },
          body: [{ imageId: imageId }],
          json: true,
        };
        request(options, (error, response, body) => {
          if (error) {
            console.log(error);
            return error;
          } else {
            const tempImgUrl = body.imageUrls[0].url;
            const temp = { string: true };
            base64.encode(tempImgUrl, temp, (err, image64) => {
              if (err) {
                console.log(err);
                return err;
              }
              return imageReturn(image64);
            });
          }
        });
     }

Passing an image from Smartsheet to Twitter was challenging because the image url generated by Smartsheet is temporary. The current solution couples Twitter with Smartsheet. An idea for future improvement would be retrieving the image and saving the base64 encoded string as soon as the tweet’s row is parsed.

Automating Sheet Tweetr

Another challenge of Sheet Tweetr was automation. Smartsheet DevRel needed Sheet Tweetr needs to run automatically, on a recurring basis. The application needed to start, pull in data from Smartsheet, look at the list of tweets, post the first “valid” tweet it finds, and then stop running. We achieved this using a serverless architecture. You can think of ‘serverless’ as a ‘function-as-a-service’. In the next article we will take a look at automating Sheet Tweetr using Webtask, an excellent serverless tool from Auth0.

Overview of Sheet Tweetr

The key to understanding what is happening under the hood of Sheet Tweetr is following the flow of information. The getListOfItems() function applies the logic needed to grab the list of tweets, determine which tweets are ‘valid’, and pass them to postTweet(). The logic controlling how Sheet Tweetr interacts with Twitter is spread out over multiple functions, but postTweet() allows Sheet Tweetr to persist until a tweet is posted successfully or no more valid tweets exist.

Now that we’ve looked under the hood of Sheet Tweetr, you’ve seen an image retrieved using the Smartsheet API and data read/written to a sheet using Smartsheet’s JavaScript SDK. Hopefully seeing the API and SDK used in the context of an application makes them easier to understand. Take a look at Sheet Tweetr on GitHub and feel free to reach out on Twitter, @SmartsheetDev, or StackOverflow using the smartsheet-api tag.