Automation

Sync subscribers between Revue & Ghost using Apps Script

Run a 2-way automated sync between your Revue account and Ghost blog while creating a backup of all your subscribers/members in a Google Sheet.

Sourabh Choraria

05 Sep 2022 • 7 min read

Sync subscribers between Revue & Ghost using Apps Script

Backstory

A few months ago I migrated my blog from being self-hosted to a paid Ghost (Pro) account and while I was at it, also found this neat little opportunity to setup Revue & connect it to my Twitter account —

I liked the idea because I could showcase my portfolio site (choraria.io) in the actual URL of my profile while not loosing the ability to showcase my work on this blog as well 😉

Problem statement

While the original/earlier versions of Ghost didn't have an option to manage memberships or send out newsletters, the latest versions do and must admit, they do a heck of an amazing job with it (no, I'm not getting paid to say that 😄) —

But, having a second source to capture new subscribers (i.e. Revue, via Twitter), meant that I'd had to come-up with a way to keep both of these sources in sync with each other —

Next, will be working on —

→ Syncing subscribers from Revue » Ghost (though webhooks would've been nice here, Revue doesn't have one 😬)
→ Maybe invest in some professional design for my blog's social media cover image (main only, not for all posts) 🤷🏽‍♀️
— Sourabh Choraria (@choraria) April 18, 2022

Idea to sync subscribers from Revue to Ghost

I didn't just want to push subscribers from Revue to Ghost but also from Ghost to Revue so that I could to brag about my numbers without actually brining it up 😛 and not have to do any of this manually.

I started with taking the easy route but soon ran into issues like these —

I bet there are ways to solve this or simply wait them out but the increased frequency of these errors just gave me more anxiety than I could handle 😅 and so, figured I'd fallback on the ol' reliable Apps Script instead.

Architecture

I've broken down the project files into 3 sections —

Config — you can store your API keys here, along with start/continue or schedule the entire workflow, as needed.
Revue — connect with Revue's API, start a CSV export (as opposed to querying the API for a list of existing subscribers), import the data from the URL and add it to the spreadsheet. You'll also see a way to syncWithGhost here so you can add new members that have subscribed form Revue to your Ghost blog.
Ghost — connect with Ghost's API, import members from Ghost to the spreadsheet and also syncWithRevue

This workflow uses a Google Sheet as the database to sync subscribers between your Revue and Ghost account and the workflow is triggered by first running the sequence with Revue via the importRevueList function — it would require us to request a list export, followed by waiting for a minute or more (depending on the size of your list) and then fetching the subscribe_url from that export.

function importRevueList() {
  let exportId = scriptProperties.getProperty("exportId");
  let timeTrigger = scriptProperties.getProperty("timeTriggerId");
  if (!exportId) {
    const startExport = REVUE_API.startListExport(REVUE_LIST_ID);
    if (startExport) {
      exportId = startExport.id;
    } else {
      console.log("REVUE_API.startListExport failed");
      return false;
    }
  }
  const exportData = REVUE_API.getExport(exportId);
  if (exportData) {
    const subscribed_url = exportData.subscribed_url;
    if (subscribed_url) {
      if (importData(subscribed_url)) {
        exportId ? scriptProperties.deleteProperty("exportId") : null;
        if (timeTrigger) {
          ScriptApp.getProjectTriggers().filter(trigger => trigger.getUniqueId() === timeTrigger ? ScriptApp.deleteTrigger(trigger) : null)
          scriptProperties.deleteProperty("timeTriggerId");
        }
        continueSync();
      } else {
        console.log("importData(subscribed_url) failed");
        return false;
      }
    } else {
      scriptProperties.setProperty("exportId", exportId);
      if (!timeTrigger) {
        timeTrigger = ScriptApp.newTrigger("importRevueList")
          .timeBased()
          .everyMinutes(1)
          .create()
          .getUniqueId();
        scriptProperties.setProperty("timeTriggerId", timeTrigger);
      }
    }
  } else {
    console.log("REVUE_API.getExport failed");
    return false;
  }
}

importRevueList function

It uses a nifty little piece of logic where a time-based trigger is automatically created if the subscribe_url isn't readily available and then checks the API every minute to see if it is. Once it's able to grab the URL, it also deletes just this specific trigger too and moves on to the next operation.

Next comes the task of importing the data from the URL. The data comes in CSV format so the operation here is pretty straightforward too —

function importData(url) {
  const res = UrlFetchApp.fetch(url, { 'muteHttpExceptions': true });
  if (res.getResponseCode() === 200) {
    let data = [];
    res.getContentText().split("\n").forEach(row => data.push(row.split(",")));
    data.pop();
    // const json = data.slice(1, data.length).map(row => data[0].reduce((obj, curr, i) => (obj[curr] = row[i], obj), {}));

    const ss = SpreadsheetApp.getActiveSpreadsheet();
    let activeSheet = ss.getSheetByName(REVUE_SHEET_NAME);
    if (!activeSheet) {
      ss.insertSheet().setName(REVUE_SHEET_NAME);
      activeSheet = ss.getSheetByName(REVUE_SHEET_NAME);
      activeSheet.getRange(1, 1, data.length, data[0].length).setValues(data);
    } else {
      const headers = activeSheet.getRange("A1:D1").getValues();
      headers.length === 4 ? null : activeSheet.getRange("A1:D1").setValues([["email", "first_name", "last_name", "created_at"]]);
      const emailDataRange = activeSheet.getRange("A2:A");
      let sheetData = [];
      data.slice(1, data.length).forEach(row => !emailDataRange.createTextFinder(row[0]).matchEntireCell(true).findNext() ? sheetData.push(row) : null)
      sheetData.length > 0 ? activeSheet.getRange(activeSheet.getLastRow() + 1, 1, sheetData.length, data[0].length).setValues(sheetData) : null;
      activeSheet.getDataRange().sort({ column: 4, ascending: false });
    }
    activeSheet.setFrozenRows(1);
    activeSheet.getMaxColumns() > 4 ? activeSheet.deleteColumns(5, activeSheet.getMaxColumns() - 5 + 1) : null;
    return true;
  } else {
    console.log({
      responseCode: res.getResponseCode(),
      responseMessage: res.getContentText(),
    });
    return false;
  }
}

importData function

Notice that it uses the createTextFinder function in order to avoid adding duplicates to the sheet and while there's more than one way to handle this, I've intentionally chosen this approach as opposed to — say — adding EVERYTHING again on the list and then removing just the duplicates. All of this is yet to be tested for scale — as in, I'm not sure how would either of these technique hold when there are a hundred-thousand+ subscribers in these accounts 😬 (stay tuned for that!).

Once we've imported subscribers from Revue, we can then move-on to importing members from Ghost and that uses the simple API-pagination approach (nothing fancy there) —

function importGhostMembers(jwt) {
  jwt = jwt ? jwt : createJwt();
  let ghostMembers = GHOST_API.getMembers(jwt);
  if (ghostMembers) {
    let data = [["email", "first_name", "last_name", "created_at"]];
    while (data.length < ghostMembers.meta.pagination.total) {
      ghostMembers.members.forEach(member => {
        let name = member.name ? member.name.split(" ") : null;
        let first_name = name ? name.shift() : '';
        let last_name = name ? name.join(" ") : '';
        data.push([member.email.toLowerCase(), first_name, last_name, member.created_at.replace("T", " ").replace("Z", "")]);
      });
      ghostMembers = GHOST_API.getMembers(jwt, ghostMembers.meta.pagination.next);
    }
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    let activeSheet = ss.getSheetByName(GHOST_SHEET_NAME);
    if (!activeSheet) {
      ss.insertSheet().setName(GHOST_SHEET_NAME);
      activeSheet = ss.getSheetByName(GHOST_SHEET_NAME);
      activeSheet.getRange(1, 1, data.length, data[0].length).setValues(data);
    } else {
      const headers = activeSheet.getRange("A1:D1").getValues();
      headers.length === 4 ? null : activeSheet.getRange("A1:D1").setValues([["email", "first_name", "last_name", "created_at"]]);
      const emailDataRange = activeSheet.getRange("A2:A");
      let sheetData = [];
      data.slice(1, data.length).forEach(row => !emailDataRange.createTextFinder(row[0]).matchEntireCell(true).findNext() ? sheetData.push(row) : null)
      sheetData.length > 0 ? activeSheet.getRange(activeSheet.getLastRow() + 1, 1, sheetData.length, data[0].length).setValues(sheetData) : null;
      activeSheet.getDataRange().sort({ column: 4, ascending: false });
    }
    activeSheet.setFrozenRows(1);
    activeSheet.getMaxColumns() > 4 ? activeSheet.deleteColumns(5, activeSheet.getMaxColumns() - 5 + 1) : null;
  } else {
    console.log("GHOST_API.getMembers failed");
  }
  return jwt;
}

importGhostMembers function

Once we have all the data from both platforms, we can then start the sync process. Here's an example for the syncWithRevue function and there's a corresponding syncWithGhost function that does pretty much the same task (but the other way around) —

function syncWithRevue() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const revueSheet = ss.getSheetByName(REVUE_SHEET_NAME);
  const ghostSheet = ss.getSheetByName(GHOST_SHEET_NAME);
  const revueData = revueSheet.getRange("A2:C").getValues();
  const ghostData = ghostSheet.getRange("A2:C").getValues();
  const revueEmails = revueData.map(cols => cols[0]);
  const ghostEmails = ghostData.map(cols => cols[0]);
  const freshEmails = ghostEmails.filter(email => !revueEmails.includes(email));
  const dataToSync = ghostData.filter(row => freshEmails.includes(row[0]))
  if (dataToSync.length > 0) {
    dataToSync.forEach(row => REVUE_API.addSubscriber(row[0], row[1] === '' ? null : row[1], row[2] === '' ? null : row[2]));
    importRevueList();
  } else {
    console.log("No new emails in Ghost to sync with Revue!");
  }
  return true;
}

syncWithRevue function

Codebase

You can access the entire project code from my GitHub repository here.

Config.gs

There are a few mandatory parameters that you'll need to fill in order to start using the project —

const REVUE_API_KEY = "...";
const REVUE_LIST_ID = "..."; // run REVUE_API.listAllLists()
const GHOST_ACCESS_TOKEN = "...";
const GHOST_ADMIN_DOMAIN = "...";

mandatory parameters

You can start by running the startSync function to test the workflow manually —

const startSync = () => importRevueList();

const continueSync = () => {
  let jwt = importGhostMembers();
  if (jwt) {
    if (syncWithGhost(jwt)) {
      if (syncWithRevue()) {
        console.log("Sync was successful!");
      } else {
        console.log("syncWithRevue() falied at startSync().");
      }
    } else {
      console.log("syncWithGhost(jwt) falied at startSync().");
    }
  } else {
    console.log("importGhostMembers() falied at startSync().");
  }
}

const scheduleSync = () => ScriptApp.newTrigger("importRevueList")
  .timeBased()
  .atHour(0)
  .nearMinute(1)
  .everyDays(1)
  .inTimezone(Session.getScriptTimeZone())
  .create();

Once your initial spreadsheet is set-up, you can then run the scheduleSync function which will automatically run the workflow every night right after midnight (based on your timezone).

Gotchas

While the scheduleSync function does what's needed —

const scheduleSync = () => ScriptApp.newTrigger("importRevueList")
  .timeBased()
  .atHour(0)
  .nearMinute(1)
  .everyDays(1)
  .inTimezone(Session.getScriptTimeZone())
  .create();

You may still want to go to the Triggers section of your project and edit this trigger's Failure notification settings from Notify me daily (which is what's set by default) to Notify me immediately (that's the one I prefer).

time-based-trigger-failure-notification-settings

2. One of the odd downsides of requesting a list export from Revue is that you'll also get an email notification indicating that your export is ready to be downloaded and given the way this workflow has been setup, there's a good chance you'll get it twice — my apologies for it in advance 🙏🏽

Credits

This would've NOT been possible without the guide from Amit Agarwal (@labnol) on generating JWTs using Google Apps Script.
The workflow also uses Kanshi TANAIKE's code snippet on converting hex 'secret' to byte array then base64Encode.
Also thanks to Riël Notermans (@rieln) for helping me solve an issue with INVALID_JWT error in Google Apps Script while working with the Ghost API.
Dozens of different Stack Overflow posts that helped me in working with 2d arrays, JSON data etc.