PixelFlow – Content Sync

// PixelFlow Content Sync v4 | Google Apps Script
// @OnlyCurrentDoc
//
// DEPLOY:
//   1. Extensions > Apps Script > paste this > Ctrl+S
//   2. Deploy > New Deployment > Web App
//   3. Execute as: Me | Who has access: Anyone
//   4. Copy the Web App URL > paste in PixelFlow plugin
//   After changes: Deploy > Manage Deployments > Edit > New version

var SECTION_BG = "#cfe2f3";

function doGet(e) {
  try {
    var action = (e && e.parameter && e.parameter.action) || "read";

    if (action === "read" || action === "probe") {
      return json({ sheets: getAllSheets(), version: "4.0.0" });
    }

    if (action === "write") {
      var raw = (e.parameter && e.parameter.data) || "{}";
      var body = JSON.parse(Utilities.newBlob(Utilities.base64Decode(raw)).getDataAsString());
      return json(writeRows(body.rows || []));
    }

    return json({ error: true, message: "Unknown action: " + action });
  } catch (err) {
    return json({ error: true, message: String(err) });
  }
}

function json(obj) {
  return ContentService.createTextOutput(JSON.stringify(obj))
    .setMimeType(ContentService.MimeType.JSON);
}

function getAllSheets() {
  var out = {};
  SpreadsheetApp.getActiveSpreadsheet().getSheets().forEach(function(sh) {
    out[sh.getName()] = parseSheet(sh);
  });
  return out;
}

function parseSheet(sheet) {
  var lastRow = sheet.getLastRow();
  if (lastRow < 2) return [];

  var lastCol = Math.max(sheet.getLastColumn(), 4);
  var vals = sheet.getRange(1, 1, lastRow, lastCol).getValues();
  var bgs = sheet.getRange(1, 1, lastRow, 1).getBackgrounds();
  var hdr = vals[0].map(function(h) { return String(h).toLowerCase().trim(); });

  var is5 = hdr.length >= 5 && hdr[2].indexOf("key") > -1;
  var ki = is5 ? 2 : 1, si = is5 ? 3 : 2, li = is5 ? 4 : 3;
  var rows = [], section = null;

  for (var i = 1; i < vals.length; i++) {
    var row = vals[i];
    var bg = String(bgs[i][0]).toLowerCase().trim();
    var c0 = String(row[0] || "").trim();
    var key = String(row[ki] || "").trim();
    var sug = String(row[si] || "").trim();
    var loc = String(row[li] || "").trim();

    if (!c0 && !key && !sug) continue;

    var isSec = bg === SECTION_BG || bg.indexOf("cfe2f3") > -1 || (c0 && !key && !sug);

    if (isSec) {
      section = c0 || key;
      rows.push({ isSection: true, section: section });
      continue;
    }

    if (!key) continue;

    rows.push({
      isSection: false,
      section: section || "",
      key: key,
      description: c0,
      suggested: sug,
      localized: loc,
      rowIdx: i + 1,
      sugCol: si + 1,
      locCol: li + 1
    });
  }
  return rows;
}

function writeRows(rows) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var updated = 0, notFound = [];
  var byTab = {};

  rows.forEach(function(r) {
    var t = r.tab || "";
    if (!byTab[t]) byTab[t] = [];
    byTab[t].push(r);
  });

  Object.keys(byTab).forEach(function(tab) {
    var sheet = ss.getSheetByName(tab);
    if (!sheet) {
      byTab[tab].forEach(function(r) { notFound.push(r.key); });
      return;
    }
    var parsed = parseSheet(sheet), km = {};
    parsed.forEach(function(r) { if (!r.isSection) km[r.key] = r; });

    byTab[tab].forEach(function(wr) {
      var m = km[wr.key];
      if (!m) {
        notFound.push(wr.key);
        return;
      }
      sheet.getRange(m.rowIdx, m.sugCol).setValue(wr.value);
      updated++;
    });
  });

  SpreadsheetApp.flush();
  return { updated: updated, notFound: notFound };
}