PR Tally

PR Media Hit Tracker ā€” C.S. Lewis & Co. Publicity

šŸ”— Connect to Google Sheets

This tracker reads and writes data to a Google Sheet so your whole team stays in sync. Follow these steps:

  1. Create a new Google Sheet (or use an existing one)
  2. Create 3 sheet tabs:
    • Current Week ā€” headers: Author | Hits | Cumulative | Status (add authors in col A from row 2)
    • Add Queue ā€” headers: Author | Notes (upcoming authors and optional start dates)
    • Removed ā€” header: Author (authors that have been cycled off)
  3. Click Share ā†’ set to “Anyone with the link” ā†’ Editor
  4. Go to Extensions ā†’ Apps Script and paste the code from the instructions below
  5. Deploy as a Web App (Execute as: Me, Access: Anyone)
  6. Copy the Web App URL and paste it below
šŸ“‹ Show Apps Script code to paste 
function doGet(e) {
  return handleRequest(e);
}

function doPost(e) {
  return handleRequest(e);
}

function handleRequest(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Current Week");
  var action = e.parameter.action;
  var output;

  // ---- Read all data (authors + queue + removed) ----
  if (action === "read") {
    var data = sheet.getDataRange().getValues();
    var authors = [];
    for (var i = 1; i < data.length; i++) {
      if (data[i][0]) {
        authors.push({
          name: data[i][0],
          hits: Number(data[i][1]) || 0,
          cumulative: Number(data[i][2]) || 0,
          status: data[i][3] ? String(data[i][3]) : ""
        });
      }
    }

    var upcoming = [];
    var queueSheet = ss.getSheetByName("Add Queue");
    if (queueSheet) {
      var qData = queueSheet.getDataRange().getValues();
      for (var i = 1; i < qData.length; i++) {
        if (qData[i][0]) {
          var entry = String(qData[i][0]);
          if (qData[i][1]) entry += " - " + String(qData[i][1]);
          upcoming.push(entry);
        }
      }
    }

    var removed = [];
    var removedSheet = ss.getSheetByName("Removed");
    if (removedSheet) {
      var rData = removedSheet.getDataRange().getValues();
      for (var i = 1; i < rData.length; i++) {
        if (rData[i][0]) {
          removed.push(String(rData[i][0]));
        }
      }
    }

    output = ContentService.createTextOutput(JSON.stringify({
      status: "ok",
      weekLabel: sheet.getName(),
      authors: authors,
      upcoming: upcoming,
      removed: removed
    }));
  }

  // ---- Add a hit ----
  else if (action === "addHit") {
    var name = e.parameter.name;
    var data = sheet.getDataRange().getValues();
    for (var i = 1; i < data.length; i++) {
      if (data[i][0] === name) {
        var newHits = (Number(data[i][1]) || 0) + 1;
        var newCum = (Number(data[i][2]) || 0) + 1;
        sheet.getRange(i + 1, 2).setValue(newHits);
        sheet.getRange(i + 1, 3).setValue(newCum);
        break;
      }
    }
    output = ContentService.createTextOutput(JSON.stringify({ status: "ok" }));
  }

  // ---- Remove a hit ----
  else if (action === "removeHit") {
    var name = e.parameter.name;
    var data = sheet.getDataRange().getValues();
    for (var i = 1; i < data.length; i++) {
      if (data[i][0] === name) {
        var curHits = Number(data[i][1]) || 0;
        var curCum = Number(data[i][2]) || 0;
        if (curHits > 0) {
          sheet.getRange(i + 1, 2).setValue(curHits - 1);
          sheet.getRange(i + 1, 3).setValue(curCum - 1);
        }
        break;
      }
    }
    output = ContentService.createTextOutput(JSON.stringify({ status: "ok" }));
  }

  // ---- Add author to Current Week ----
  else if (action === "addAuthor") {
    var name = e.parameter.name;
    var lastRow = sheet.getLastRow();
    sheet.getRange(lastRow + 1, 1).setValue(name);
    sheet.getRange(lastRow + 1, 2).setValue(0);
    sheet.getRange(lastRow + 1, 3).setValue(0);
    output = ContentService.createTextOutput(JSON.stringify({ status: "ok" }));
  }

  // ---- Remove author from Current Week ----
  else if (action === "removeAuthor") {
    var name = e.parameter.name;
    var data = sheet.getDataRange().getValues();
    for (var i = 1; i < data.length; i++) {
      if (data[i][0] === name) {
        sheet.deleteRow(i + 1);
        break;
      }
    }
    output = ContentService.createTextOutput(JSON.stringify({ status: "ok" }));
  }

  // ---- Add to queue ----
  else if (action === "addToQueue") {
    var name = e.parameter.name;
    var notes = e.parameter.notes || "";
    var queueSheet = ss.getSheetByName("Add Queue");
    if (!queueSheet) {
      queueSheet = ss.insertSheet("Add Queue");
      queueSheet.getRange(1, 1).setValue("Author");
      queueSheet.getRange(1, 2).setValue("Notes");
    }
    var lastRow = queueSheet.getLastRow();
    queueSheet.getRange(lastRow + 1, 1).setValue(name);
    queueSheet.getRange(lastRow + 1, 2).setValue(notes);
    output = ContentService.createTextOutput(JSON.stringify({ status: "ok" }));
  }

  // ---- Remove from queue ----
  else if (action === "removeFromQueue") {
    var name = e.parameter.name;
    var queueSheet = ss.getSheetByName("Add Queue");
    if (queueSheet) {
      var qData = queueSheet.getDataRange().getValues();
      for (var i = 1; i < qData.length; i++) {
        if (String(qData[i][0]) === name) {
          queueSheet.deleteRow(i + 1);
          break;
        }
      }
    }
    output = ContentService.createTextOutput(JSON.stringify({ status: "ok" }));
  }

  // ---- Add to removed list ----
  else if (action === "addToRemoved") {
    var name = e.parameter.name;
    var removedSheet = ss.getSheetByName("Removed");
    if (!removedSheet) {
      removedSheet = ss.insertSheet("Removed");
      removedSheet.getRange(1, 1).setValue("Author");
    }
    var lastRow = removedSheet.getLastRow();
    removedSheet.getRange(lastRow + 1, 1).setValue(name);
    output = ContentService.createTextOutput(JSON.stringify({ status: "ok" }));
  }

  // ---- Remove from removed list ----
  else if (action === "removeFromRemoved") {
    var name = e.parameter.name;
    var removedSheet = ss.getSheetByName("Removed");
    if (removedSheet) {
      var rData = removedSheet.getDataRange().getValues();
      for (var i = 1; i < rData.length; i++) {
        if (String(rData[i][0]) === name) {
          removedSheet.deleteRow(i + 1);
          break;
        }
      }
    }
    output = ContentService.createTextOutput(JSON.stringify({ status: "ok" }));
  }

  // ---- Set author status ----
  else if (action === "setStatus") {
    var name = e.parameter.name;
    var newStatus = e.parameter.status || "";
    var data = sheet.getDataRange().getValues();
    for (var i = 1; i < data.length; i++) {
      if (data[i][0] === name) {
        sheet.getRange(i + 1, 4).setValue(newStatus);
        break;
      }
    }
    output = ContentService.createTextOutput(JSON.stringify({ status: "ok" }));
  }

  else {
    output = ContentService.createTextOutput(JSON.stringify({
      status: "error",
      message: "Unknown action"
    }));
  }

  output.setMimeType(ContentService.MimeType.JSON);
  return output;
}