Build a BlueSky bot using Google Sheets

Google Sheets is a pretty useful tool for Cloud Computing when paired with the Apps Script programming language. Also it's 100% free.

Here's the basics for creating a BlueSky bot using Google Sheets

1. Think of an idea for a bot
2. Create a Gmail account
3. Create a Bsky account (or use one you have)
4. Create a Google Sheet and fill in any data you want to use
5. Copy over some AppsScript code from a template, making a few adjustments
6. Deploy the code
7. Set up the trigger

This example will make a bot that posts a line from Hamlet every hour.

Think of an idea for a bot

This may be the easiest or hardest step, it's one thing I can't do for you. The bot in this demo will post a line from Hamlet every hour, you can use any literary work or have it follow much more sophisticated logic

Create a Gmail account

You gotta have one to build this bot. You won't need to enable GCP or anything so this should be 100% free.

Create a BlueSky account

You can just reuse your existing account if you don't mind the bot posts getting mixed in with your own, or you can make a profile associated with yours like @hamlet-bot.bsky.social.

Create a Google Sheet and add data

For this example, I'll add a row for each line in the play in one column called 'Lines' and another column for tracking progress called 'Posted' which I'll leave empty

Copy over the AppScript code making a few adjustments

This is the coding part, all you need to do is to replace two (or three) values

1. Replace PUT_YOUR_BOTS_NAME_HERE with your bot's name like 'hamlet-bot.bsky.social'
2. Replace PUT_YOUR_BOTS_PASSWORD_HERE with your bot's password
3. If your spreadsheet is not named 'Sheet1' replace the reference with the name of your sheet

• Under the Extensions menu, select Apps Script
• In the editor replace everything in Code.gs with the code below

const USER = PUT_YOUR_BOTS_NAME_HERE;
const PW = PUT_YOUR_BOTS_PASSWORD_HERE;

// Helper function for making requests in AppsScript
function getOptions(payload, token) {
  const baseOptions = {
    'method': 'post',
    'contentType': 'application/json',
    'headers': {
      'Accept': 'application/json'
    },
  }
  if (token !== '') {
    baseOptions.headers['Authorization'] = 'Bearer ' + token;
  }
  baseOptions['payload'] = payload;
  return baseOptions;
}

// Helper function for signing into Bluesky, returns your access token and the service
// endpoint for the bot's did
function signIn() {
  const url = 'https://public.api.bsky.app/xrpc/com.atproto.server.createSession';
  let options = getOptions(JSON.stringify({
    "identifier": USER,
    "password": PW,
  }), '');
  const resp = UrlFetchApp.fetch(url, options);
  const data = JSON.parse(resp.getContentText());
  return {
    token: data.accessJwt, did: data.did,
    serviceEndpoint: data.didDoc.service[0].serviceEndpoint ?? ''
  };
};


// This is what creates your post's data, you can customize this function to do anything
// you'd like.
// Returns
// The content to post or nothing if it's not time to post yet
function getPostContent() {
  // Get the sheet with the post data, replace 'Sheet1' here if you have named it something else
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  const values = sheet.getDataRange().getValues();
  // Convert the Sheet's data into an easier to use format
  const data = values.slice(1).map(
    (v) => ({ phrase: v[0], lastPosted: Date.parse(v[1]) })
  );
  // Find the first unposted row
  const unPosted = data.filter((e) => isNaN(e.lastPosted));
  if(unPosted.length == 0) {
    return undefined;
  }
  const post = unPosted[0].phrase;
  // Write out the date that the post was made to the sheet
  for (var i = 0; i < values.length; i++) {
    if (values[i][0] == post) {
      sheet.getRange(i + 1, 2).setValue(
        Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'")
      );
      break;
    }
  }
  return post;
}

// Function that gets at each interval (daily/hourly/etc) to sign in, determine
// if there's content to post then posts it
function writePost() {
  const { token, did, serviceEndpoint } = signIn();
  console.log(serviceEndpoint);
  const url = serviceEndpoint + '/xrpc/com.atproto.repo.createRecord';
  const now = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'");
  const postText = getPostContent();
  if (!postText) {
    return;
  }
  console.log(`letting ${postText} fly`);
  let options = getOptions(
    JSON.stringify({
      "repo": did,
      "collection": "app.bsky.feed.post",
      "record": {
        "$type": "app.bsky.feed.post",
        "text": postText,
        "createdAt": now,
      },
    })
    , token);
  const resp = UrlFetchApp.fetch(url, options);
} 

Deploy the code

1. Click 'DEPLOY' in the upper-right
2. Then select New Deployment
3. Select 'Web App' as the type
4. Leave the other options as-is and select 'Delpoy'

Select the trigger

1. From the right side, select 'Trigger'
2. Under the function to run select 'writePost'
3. Under Event Source select 'Time driven'
4. Then select Hour Timer and Every Hour (or how ever frequently you'd like
5. Select 'Save'

That's it, you can DM me with any questions. Happy botting!