PayNexus Google Sheet Instructions

Use two different private codes.Your family connection code goes in Code.gs and the family PayNexus app. The app creates a separate restricted advisor code. Never give your family connection code to an advisor.

Before you begin

A personal Google account that can deploy an Apps Script web app.
The PayNexus family app open on the first device you want to connect.
A password manager or another safe place to store your private family connection code and deployment URL.
The PayNexus Advisor app only if you plan to share summaries with an advisor.

Keep the Google Sheet itself set to Restricted. The web-app connector is deployed so PayNexus can reach it, while your private code rejects unauthorized family sync requests.

Part 1: Create the PayNexus Google Sheet

Create a blank Sheet

In Google Drive, create a blank Google Sheet. You may name it PayNexus Family Bill Hub; the setup function will also apply that name automatically. Leave the Sheet’s sharing setting as Restricted.

Open Apps Script

Inside the Sheet, select Extensions → Apps Script. Rename the project PayNexus Connector. Open Code.gs and delete all sample code.

Copy the complete connector

Use Copy Code.gs in the code box below. Return to Apps Script and paste it into the empty Code.gs editor. Copy from the first line through the final brace.

Create your private family connection code

Near the top of Code.gs, find:

const PRIVATE_CONNECTION_CODE = 'CHANGE-THIS-TO-YOUR-PRIVATE-CODE';

Replace only CHANGE-THIS-TO-YOUR-PRIVATE-CODE with a new random code at least 32 characters long. Keep the quotation marks. Do not use your Google password, name, address, or an advisor code. Save the project.

Run the one-time setup

At the top of Apps Script, open the function menu.
Select setupPayNexus.
Click Run.
When Google asks for authorization, choose your account, review the permissions, and allow the script to manage this Sheet.
Return to the Sheet and refresh it.

You should see tabs including Bills, Bill Templates, Planned Expenses, Payments, Bill Activity, Payment Methods, Settings, Deleted Records, Change Log, and Migration Backups.

Deploy the web app

In Apps Script, choose Deploy → New deployment.
Click the gear beside “Select type” and choose Web app.
Set Execute as to Me.
Set Who has access to Anyone.
Click Deploy and complete any authorization prompt.
Copy the Web App URL ending in /exec. Do not use a URL ending in /dev.

Connect the family PayNexus app

Open PayNexus and tap the gear.
Under Google Sheets backup & sharing, paste the Web App /exec URL.
Choose the automatic-sync interval.
Enter the exact private family connection code from Code.gs—without quotation marks.
Tap Save & sync now.
Wait for Synced successfully below the button.

Confirm the first sync

Add a small test bill in PayNexus, tap the header Sync button, and wait for completion. Refresh the Google Sheet and confirm the bill appears in the Bills tab.

Complete PayNexus Code.gs

This copy contains a placeholder—not anyone’s real secret. Add your own private code before deploying.

/**
 * PayNexus row-based Google Sheets storage
 *
 * SETUP
 * 1. Replace CHANGE-THIS-TO-YOUR-PRIVATE-CODE with your existing private code.
 * 2. Save the project.
 * 3. Select setupPayNexus and click Run once.
 * 4. Update the existing Web app deployment to a new version.
 *
 * The existing /exec URL remains the same when the existing deployment is updated.
 * The legacy "PayNexus Data" tab is preserved and is never cleared by this script.
 */
const PRIVATE_CONNECTION_CODE = 'CHANGE-THIS-TO-YOUR-PRIVATE-CODE';
const SPREADSHEET_FILE_NAME = 'PayNexus Family Bill Hub';
const API_SCHEMA_VERSION = 3;
const STORAGE_SCHEMA_VERSION = 3;
const CHUNK_SIZE = 45000;

const LEGACY_DATA_SHEET = 'PayNexus Data';
const OLDER_LEGACY_DATA_SHEET = 'Family Bill Care Data';
const BILLS_SHEET = 'Bills';
const TEMPLATES_SHEET = 'Bill Templates';
const PLANS_SHEET = 'Planned Expenses';
const PAYMENTS_SHEET = 'Payments';
const ACTIVITY_SHEET = 'Bill Activity';
const METHODS_SHEET = 'Payment Methods';
const SETTINGS_SHEET = 'Settings';
const DELETED_SHEET = 'Deleted Records';
const CHANGE_LOG_SHEET = 'Change Log';
const BACKUPS_SHEET = 'Migration Backups';

const BILL_HEADERS = ['Bill ID','Series ID','Bill Name','Bill Total','Amount Paid','Difference','Due Date','Expected Date','Stage','Frequency','Amount Type','Account / Reference','Notes','Updated At','Created At','Record JSON'];
const SIMPLE_RECORD_HEADERS = ['Record ID','Name / Label','Updated At','Record JSON'];
const PAYMENT_HEADERS = ['Payment ID','Bill ID','Amount Paid','Payment Method','Check Number','Confirmation Number','Payment Date','Cleared Date','Entered By','Updated At','Record JSON'];
const ACTIVITY_HEADERS = ['Activity ID','Bill ID','Stage','Completed By','Completed At','Record JSON'];
const METHOD_HEADERS = ['Method ID','Method Name','Active','Updated At','Record JSON'];
const SETTING_HEADERS = ['Key','Value JSON','Updated At'];
const DELETED_HEADERS = ['Record ID','Deleted At','Updated At','Record JSON'];
const CHANGE_HEADERS = ['Timestamp','Action','Device / User','Revision','Details'];
const BACKUP_HEADERS = ['Backup ID','Saved At','Reason','From Schema','Chunk','Original Data — do not edit'];

function setupPayNexus() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  if (spreadsheet.getName() !== SPREADSHEET_FILE_NAME) {
    spreadsheet.rename(SPREADSHEET_FILE_NAME);
  }

  const result = ensureDatabase_();

  const message =
    'PayNexus storage is ready. ' +
    result.billCount +
    ' bills available.' +
    (
      result.migrated
        ? ' Legacy data was copied safely.'
        : ''
    );

  spreadsheet.toast(
    message,
    'PayNexus setup complete',
    10
  );

  console.log(
    message +
    ' The original PayNexus Data tab was left unchanged.'
  );
}

function doGet() {
  return json_({
    ok: true,
    app: 'PayNexus',
    schemaVersion: API_SCHEMA_VERSION,
    storageSchemaVersion: STORAGE_SCHEMA_VERSION,
    message: 'PayNexus row-based sync service is running.'
  });
}

function doPost(e) {
  const lock = LockService.getScriptLock();

  try {
    lock.waitLock(30000);

    const request = JSON.parse(
      (e.postData && e.postData.contents) || '{}'
    );

    if (request.action === 'advisorSummary') {
      return advisorSummaryResponse_(request);
    }

    if (request.action !== 'sync') {
      throw new Error('Invalid request.');
    }

    if (
      !request.key ||
      request.key !== PRIVATE_CONNECTION_CODE
    ) {
      throw new Error(
        'The private connection code is not correct.'
      );
    }

    if (
      Number(request.schemaVersion || 1) >
      API_SCHEMA_VERSION
    ) {
      throw new Error(
        'This Code.gs needs to be updated before it can accept that PayNexus app version.'
      );
    }

    ensureDatabase_();

    const stored = readDatabase_();

    const incoming = normalizeSnapshot_({
      bills: Array.isArray(request.bills)
        ? request.bills
        : [],

      tombstones: Array.isArray(request.tombstones)
        ? request.tombstones
        : [],

      templates: Array.isArray(request.templates)
        ? request.templates
        : [],

      plans: Array.isArray(request.plans)
        ? request.plans
        : [],

      payments: Array.isArray(request.payments)
        ? request.payments
        : [],

      paymentMethods: Array.isArray(request.paymentMethods)
        ? request.paymentMethods
        : [],

      settings:
        request.settings || defaultSettings_()
    });

    const merged = mergeSnapshots_(
      stored,
      incoming
    );

    const written = writeDatabase_(merged);

    let advisorWarning = '';

    if (
      request.advisorAccess &&
      typeof request.advisorAccess === 'object'
    ) {
      try {
        const advisorAccess =
          sanitizeAdvisorAccess_(
            request.advisorAccess
          );

        writeSetting_(
          'advisorAccess',
          advisorAccess,
          new Date().toISOString()
        );

        writeSetting_(
          'advisorSummary',
          sanitizeAdvisorSummary_(
            request.advisorSummary,
            advisorAccess.displayName
          ),
          new Date().toISOString()
        );
      } catch (advisorError) {
        advisorWarning =
          advisorError.message ||
          'Advisor summary was not saved.';
      }
    }

    appendChangeLog_(
      'Sync completed',
      request.user || '',
      written.revision,
      written.bills.length + ' bills stored'
    );

    return json_({
      ok: true,
      schemaVersion: API_SCHEMA_VERSION,
      storageSchemaVersion:
        STORAGE_SCHEMA_VERSION,
      revision: written.revision,
      bills: written.bills,
      tombstones: written.tombstones,
      templates: written.templates,
      plans: written.plans,
      payments: written.payments,
      paymentMethods: written.paymentMethods,
      settings: written.settings,
      advisorWarning: advisorWarning
    });

  } catch (error) {
    return json_({
      ok: false,
      error: error.message
    });

  } finally {
    try {
      lock.releaseLock();
    } catch (_) {}
  }
}

function advisorSummaryResponse_(request) {
  ensureDatabase_();

  const access =
    readSetting_('advisorAccess') || {};

  if (
    !access.enabled ||
    !request.token ||
    String(request.token) !==
      String(access.token || '')
  ) {
    throw new Error(
      'Advisor access is not authorized.'
    );
  }

  const summary = sanitizeAdvisorSummary_(
    readSetting_('advisorSummary'),
    access.displayName
  );

  return json_({
    ok: true,
    app: 'PayNexus Advisor',
    schemaVersion: 1,
    summary: summary
  });
}

function sanitizeAdvisorAccess_(source) {
  source = source || {};

  const token = String(
    source.token || ''
  ).slice(0, 160);

  return {
    enabled: !!source.enabled &&
      token.length >= 20,
    displayName: String(
      source.displayName || 'Household'
    ).slice(0, 100),
    token: token
  };
}

function sanitizeAdvisorSummary_(source, displayName) {
  source = source || {};

  const months = Array.isArray(source.months)
    ? source.months.slice(0, 24).map(
        function (item) {
          item = item || {};

          return {
            month: /^\d{4}-\d{2}$/.test(
              String(item.month || '')
            )
              ? String(item.month)
              : '',
            forecast:
              Math.max(
                0,
                Number(item.forecast) || 0
              ),
            lastYearActual:
              Math.max(
                0,
                Number(
                  item.lastYearActual
                ) || 0
              )
          };
        }
      ).filter(function (item) {
        return !!item.month;
      })
    : [];

  const alerts = Array.isArray(source.alerts)
    ? source.alerts.slice(0, 25).map(
        function (item) {
          item = item || {};

          return {
            id: String(
              item.id || ''
            ).slice(0, 100),
            at: String(
              item.at || ''
            ).slice(0, 40),
            reason: String(
              item.reason || ''
            ).slice(0, 120),
            message: String(
              item.message || ''
            ).slice(0, 500)
          };
        }
      )
    : [];

  return {
    version: 1,
    displayName: String(
      displayName ||
      source.displayName ||
      'Household'
    ).slice(0, 100),
    updatedAt: String(
      source.updatedAt || ''
    ).slice(0, 40),
    months: months,
    alerts: alerts
  };
}

function ensureDatabase_() {
  const spreadsheet =
    SpreadsheetApp.getActiveSpreadsheet();

  if (
    spreadsheet.getName() !==
    SPREADSHEET_FILE_NAME
  ) {
    spreadsheet.rename(
      SPREADSHEET_FILE_NAME
    );
  }

  ensureSheet_(
    BILLS_SHEET,
    BILL_HEADERS
  );

  ensureSheet_(
    TEMPLATES_SHEET,
    SIMPLE_RECORD_HEADERS
  );

  ensureSheet_(
    PLANS_SHEET,
    SIMPLE_RECORD_HEADERS
  );

  ensureSheet_(
    PAYMENTS_SHEET,
    PAYMENT_HEADERS
  );

  ensureSheet_(
    ACTIVITY_SHEET,
    ACTIVITY_HEADERS
  );

  ensureSheet_(
    METHODS_SHEET,
    METHOD_HEADERS
  );

  ensureSheet_(
    SETTINGS_SHEET,
    SETTING_HEADERS
  );

  ensureSheet_(
    DELETED_SHEET,
    DELETED_HEADERS
  );

  ensureSheet_(
    CHANGE_LOG_SHEET,
    CHANGE_HEADERS
  );

  ensureSheet_(
    BACKUPS_SHEET,
    BACKUP_HEADERS
  );

  const initialized =
    readSetting_('storageInitialized') === true;

  let migrated = false;

  if (!initialized) {
    const legacy = readLegacySnapshot_();

    if (legacy) {
      const migratedData =
        normalizeSnapshot_(legacy.data);

      if (
        migratedData.bills.length !==
        legacy.originalBillCount
      ) {
        throw new Error(
          'Migration verification failed. The original data was not changed.'
        );
      }

      if (
        readSetting_('legacyBackupSaved') !==
        true
      ) {
        backupRawData_(
          legacy.raw,
          'Before row-based migration',
          legacy.fromSchema
        );

        writeSetting_(
          'legacyBackupSaved',
          true,
          new Date().toISOString()
        );
      }

      migratedData.revision = 0;

      const written =
        writeDatabase_(migratedData);

      SpreadsheetApp.flush();

      if (!databaseCountsMatch_(written)) {
        throw new Error(
          'Migration verification did not finish. Run setupPayNexus again; the original PayNexus Data tab remains unchanged.'
        );
      }

      appendChangeLog_(
        'Legacy migration completed',
        '',
        written.revision,
        written.bills.length +
          ' bills migrated'
      );

      migrated = true;
    }

    writeSetting_(
      'storageInitialized',
      true,
      new Date().toISOString()
    );

    writeSetting_(
      'storageSchemaVersion',
      STORAGE_SCHEMA_VERSION,
      new Date().toISOString()
    );
  }

  return {
    migrated: migrated,
    billCount: countDataRows_(BILLS_SHEET)
  };
}

function readLegacySnapshot_() {
  const spreadsheet =
    SpreadsheetApp.getActiveSpreadsheet();

  const sheet =
    spreadsheet.getSheetByName(
      LEGACY_DATA_SHEET
    ) ||
    spreadsheet.getSheetByName(
      OLDER_LEGACY_DATA_SHEET
    );

  if (!sheet) {
    return null;
  }

  const count =
    Number(
      sheet.getRange('D3').getValue()
    ) || 0;

  if (!count) {
    return null;
  }

  const raw = sheet
    .getRange(3, 1, count, 1)
    .getValues()
    .map(function (row) {
      return String(row[0] || '');
    })
    .join('');

  let parsed;

  try {
    parsed = JSON.parse(raw);
  } catch (error) {
    throw new Error(
      'The legacy PayNexus data could not be read. No migration was performed. ' +
      error.message
    );
  }

  if (
    !parsed ||
    !Array.isArray(parsed.bills)
  ) {
    throw new Error(
      'The legacy PayNexus data is missing its bills list. No migration was performed.'
    );
  }

  return {
    raw: raw,
    data: parsed,
    originalBillCount: parsed.bills.length,
    fromSchema:
      Number(parsed.schemaVersion) || 1
  };
}

function readDatabase_() {
  return normalizeSnapshot_({
    bills: readJsonRecords_(
      BILLS_SHEET,
      BILL_HEADERS.length - 1
    ),

    templates: readJsonRecords_(
      TEMPLATES_SHEET,
      SIMPLE_RECORD_HEADERS.length - 1
    ),

    plans: readJsonRecords_(
      PLANS_SHEET,
      SIMPLE_RECORD_HEADERS.length - 1
    ),

    payments: readJsonRecords_(
      PAYMENTS_SHEET,
      PAYMENT_HEADERS.length - 1
    ),

    paymentMethods: readJsonRecords_(
      METHODS_SHEET,
      METHOD_HEADERS.length - 1
    ),

    tombstones: readJsonRecords_(
      DELETED_SHEET,
      DELETED_HEADERS.length - 1
    ),

    settings: {
      value:
        readSetting_('sharedSettings') || {},

      updatedAt:
        readSetting_(
          'sharedSettingsUpdatedAt'
        ) ||
        new Date(0).toISOString()
    },

    revision:
      Number(
        readSetting_('revision')
      ) || 0
  });
}

function writeDatabase_(data) {
  const snapshot =
    normalizeSnapshot_(data);

  const savedAt =
    new Date().toISOString();

  snapshot.revision =
    Math.max(
      Number(data.revision) || 0,
      Number(
        readSetting_('revision')
      ) || 0
    ) + 1;

  writeRows_(
    BILLS_SHEET,
    BILL_HEADERS,
    snapshot.bills.map(billRow_)
  );

  writeRows_(
    TEMPLATES_SHEET,
    SIMPLE_RECORD_HEADERS,
    snapshot.templates.map(
      simpleRecordRow_
    )
  );

  writeRows_(
    PLANS_SHEET,
    SIMPLE_RECORD_HEADERS,
    snapshot.plans.map(
      simpleRecordRow_
    )
  );

  writeRows_(
    PAYMENTS_SHEET,
    PAYMENT_HEADERS,
    snapshot.payments.map(
      paymentRow_
    )
  );

  writeRows_(
    METHODS_SHEET,
    METHOD_HEADERS,
    snapshot.paymentMethods.map(
      methodRow_
    )
  );

  writeRows_(
    DELETED_SHEET,
    DELETED_HEADERS,
    snapshot.tombstones.map(
      deletedRow_
    )
  );

  writeRows_(
    ACTIVITY_SHEET,
    ACTIVITY_HEADERS,
    activityRows_(snapshot.bills)
  );

  writeSetting_(
    'sharedSettings',
    snapshot.settings.value || {},
    snapshot.settings.updatedAt ||
      savedAt
  );

  writeSetting_(
    'sharedSettingsUpdatedAt',
    snapshot.settings.updatedAt ||
      savedAt,
    savedAt
  );

  writeSetting_(
    'revision',
    snapshot.revision,
    savedAt
  );

  writeSetting_(
    'storageSchemaVersion',
    STORAGE_SCHEMA_VERSION,
    savedAt
  );

  writeSetting_(
    'lastSynced',
    savedAt,
    savedAt
  );

  return snapshot;
}

function normalizeSnapshot_(source) {
  source = source || {};

  return {
    storageSchemaVersion:
      STORAGE_SCHEMA_VERSION,

    bills:
      validRecords_(source.bills),

    templates:
      validRecords_(source.templates),

    plans:
      validRecords_(source.plans),

    payments:
      validRecords_(source.payments),

    paymentMethods:
      validRecords_(
        source.paymentMethods
      ),

    tombstones:
      validRecords_(
        source.tombstones
      ),

    settings:
      source.settings ||
      defaultSettings_(),

    revision:
      Number(source.revision) || 0
  };
}

function mergeSnapshots_(left, right) {
  const tombstones = mergeRecords_(
    left.tombstones,
    right.tombstones
  );

  const deletedById = {};

  tombstones.forEach(function (item) {
    deletedById[item.id] = item;
  });

  const bills = mergeRecords_(
    left.bills,
    right.bills
  ).filter(function (bill) {
    const deleted =
      deletedById[bill.id];

    return (
      !deleted ||
      recordTime_(bill) >
        recordTime_(deleted)
    );
  });

  const leftSettings =
    left.settings ||
    defaultSettings_();

  const rightSettings =
    right.settings ||
    defaultSettings_();

  return normalizeSnapshot_({
    bills: bills,
    tombstones: tombstones,

    templates: mergeRecords_(
      left.templates,
      right.templates
    ),

    plans: mergeRecords_(
      left.plans,
      right.plans
    ),

    payments: mergeRecords_(
      left.payments,
      right.payments
    ),

    paymentMethods: mergeRecords_(
      left.paymentMethods,
      right.paymentMethods
    ),

    settings:
      String(
        rightSettings.updatedAt || ''
      ) >=
      String(
        leftSettings.updatedAt || ''
      )
        ? rightSettings
        : leftSettings,

    revision: Math.max(
      Number(left.revision) || 0,
      Number(right.revision) || 0
    )
  });
}

function mergeRecords_(left, right) {
  const map = {};

  validRecords_(left)
    .concat(validRecords_(right))
    .forEach(function (record) {
      const previous =
        map[record.id];

      if (
        !previous ||
        recordTime_(record) >=
          recordTime_(previous)
      ) {
        map[record.id] = record;
      }
    });

  return Object.keys(map).map(
    function (id) {
      return map[id];
    }
  );
}

function validRecords_(records) {
  return Array.isArray(records)
    ? records.filter(
        function (record) {
          return record && record.id;
        }
      )
    : [];
}

function recordTime_(record) {
  return String(
    record.updatedAt ||
    record.deletedAt ||
    record.createdAt ||
    ''
  );
}

function defaultSettings_() {
  return {
    value: {},
    updatedAt:
      new Date(0).toISOString()
  };
}

function billRow_(bill) {
  const total =
    numberOrBlank_(bill.amount);

  const paid =
    numberOrBlank_(bill.paidAmount);

  const difference =
    total !== '' && paid !== ''
      ? paid - total
      : '';

  return [
    bill.id,
    bill.seriesId || '',
    bill.name || '',
    total,
    paid,
    difference,
    bill.due || '',
    bill.expected || '',
    bill.stage == null
      ? 0
      : bill.stage,
    bill.frequency || 'none',
    bill.amountMode || 'fixed',
    bill.account || '',
    bill.notes || '',
    bill.updatedAt || '',
    bill.createdAt || '',
    JSON.stringify(bill)
  ];
}

function simpleRecordRow_(record) {
  return [
    record.id,
    record.name ||
      record.label ||
      '',
    record.updatedAt || '',
    JSON.stringify(record)
  ];
}

function paymentRow_(payment) {
  return [
    payment.id,
    payment.billId || '',
    numberOrBlank_(
      payment.amountPaid
    ),
    payment.method ||
      payment.paymentMethod ||
      '',
    payment.checkNumber || '',
    payment.confirmationNumber || '',
    payment.paymentDate || '',
    payment.clearedDate || '',
    payment.enteredBy || '',
    payment.updatedAt || '',
    JSON.stringify(payment)
  ];
}

function methodRow_(method) {
  return [
    method.id,
    method.name || '',
    method.active === false
      ? 'No'
      : 'Yes',
    method.updatedAt || '',
    JSON.stringify(method)
  ];
}

function deletedRow_(item) {
  return [
    item.id,
    item.deletedAt || '',
    item.updatedAt ||
      item.deletedAt ||
      '',
    JSON.stringify(item)
  ];
}

function activityRows_(bills) {
  const rows = [];

  bills.forEach(function (bill) {
    (
      Array.isArray(bill.events)
        ? bill.events
        : []
    ).forEach(
      function (event, index) {
        const activity = {
          id:
            event.id ||
            bill.id +
              '-stage-' +
              event.stage +
              '-' +
              index,

          billId: bill.id,
          stage: event.stage,
          by: event.by || '',
          at: event.at || ''
        };

        rows.push([
          activity.id,
          bill.id,
          activity.stage,
          activity.by,
          activity.at,
          JSON.stringify(activity)
        ]);
      }
    );
  });

  return rows;
}

function readJsonRecords_(
  sheetName,
  jsonColumnIndex
) {
  const sheet =
    SpreadsheetApp
      .getActiveSpreadsheet()
      .getSheetByName(sheetName);

  if (
    !sheet ||
    sheet.getLastRow() <= 1
  ) {
    return [];
  }

  const rows = sheet
    .getRange(
      2,
      1,
      sheet.getLastRow() - 1,
      sheet.getLastColumn()
    )
    .getValues();

  return rows
    .map(function (row, index) {
      const raw =
        row[jsonColumnIndex];

      if (!raw) {
        return null;
      }

      try {
        return JSON.parse(raw);
      } catch (error) {
        throw new Error(
          'Invalid JSON in ' +
          sheetName +
          ' row ' +
          (index + 2) +
          '. No data was written.'
        );
      }
    })
    .filter(Boolean);
}

function writeRows_(
  sheetName,
  headers,
  rows
) {
  const sheet =
    ensureSheet_(sheetName, headers);

  const existing = Math.max(
    sheet.getLastRow() - 1,
    0
  );

  if (existing) {
    sheet
      .getRange(
        2,
        1,
        existing,
        Math.max(
          sheet.getLastColumn(),
          headers.length
        )
      )
      .clearContent();
  }

  if (rows.length) {
    sheet
      .getRange(
        2,
        1,
        rows.length,
        headers.length
      )
      .setValues(rows);
  }
}

function ensureSheet_(name, headers) {
  const spreadsheet =
    SpreadsheetApp.getActiveSpreadsheet();

  let sheet =
    spreadsheet.getSheetByName(name);

  let created = false;

  if (!sheet) {
    sheet =
      spreadsheet.insertSheet(name);

    created = true;
  }

  const current = sheet
    .getRange(
      1,
      1,
      1,
      headers.length
    )
    .getValues()[0];

  const different =
    headers.some(
      function (header, index) {
        return (
          current[index] !== header
        );
      }
    );

  if (different) {
    sheet
      .getRange(
        1,
        1,
        1,
        headers.length
      )
      .setValues([headers]);
  }

  if (created || different) {
    sheet.setFrozenRows(1);

    sheet
      .getRange(
        1,
        1,
        1,
        headers.length
      )
      .setFontWeight('bold')
      .setBackground('#174d5b')
      .setFontColor('#ffffff');
  }

  return sheet;
}

function readSetting_(key) {
  const sheet =
    SpreadsheetApp
      .getActiveSpreadsheet()
      .getSheetByName(
        SETTINGS_SHEET
      );

  if (
    !sheet ||
    sheet.getLastRow() <= 1
  ) {
    return null;
  }

  const values = sheet
    .getRange(
      2,
      1,
      sheet.getLastRow() - 1,
      3
    )
    .getValues();

  for (
    let i = 0;
    i < values.length;
    i++
  ) {
    if (values[i][0] === key) {
      try {
        return JSON.parse(
          values[i][1]
        );
      } catch (_) {
        return values[i][1];
      }
    }
  }

  return null;
}

function writeSetting_(
  key,
  value,
  updatedAt
) {
  const sheet =
    ensureSheet_(
      SETTINGS_SHEET,
      SETTING_HEADERS
    );

  const lastRow =
    sheet.getLastRow();

  if (lastRow > 1) {
    const keys = sheet
      .getRange(
        2,
        1,
        lastRow - 1,
        1
      )
      .getValues();

    for (
      let i = 0;
      i < keys.length;
      i++
    ) {
      if (keys[i][0] === key) {
        sheet
          .getRange(
            i + 2,
            1,
            1,
            3
          )
          .setValues([[
            key,
            JSON.stringify(value),
            updatedAt ||
              new Date().toISOString()
          ]]);

        return;
      }
    }
  }

  sheet.appendRow([
    key,
    JSON.stringify(value),
    updatedAt ||
      new Date().toISOString()
  ]);
}

function backupRawData_(
  raw,
  reason,
  fromSchema
) {
  const sheet =
    ensureSheet_(
      BACKUPS_SHEET,
      BACKUP_HEADERS
    );

  const backupId =
    Utilities.getUuid();

  const savedAt =
    new Date().toISOString();

  const chunks = [];

  for (
    let i = 0;
    i < raw.length;
    i += CHUNK_SIZE
  ) {
    chunks.push(
      raw.slice(
        i,
        i + CHUNK_SIZE
      )
    );
  }

  if (!chunks.length) {
    chunks.push('');
  }

  const rows = chunks.map(
    function (chunk, index) {
      return [
        backupId,
        savedAt,
        reason,
        fromSchema || 1,
        (index + 1) +
          ' of ' +
          chunks.length,
        chunk
      ];
    }
  );

  sheet
    .getRange(
      sheet.getLastRow() + 1,
      1,
      rows.length,
      BACKUP_HEADERS.length
    )
    .setValues(rows);
}

function appendChangeLog_(
  action,
  user,
  revision,
  details
) {
  ensureSheet_(
    CHANGE_LOG_SHEET,
    CHANGE_HEADERS
  ).appendRow([
    new Date().toISOString(),
    action,
    user || '',
    revision || '',
    details || ''
  ]);
}

function countDataRows_(sheetName) {
  const sheet =
    SpreadsheetApp
      .getActiveSpreadsheet()
      .getSheetByName(sheetName);

  return sheet
    ? Math.max(
        sheet.getLastRow() - 1,
        0
      )
    : 0;
}

function databaseCountsMatch_(snapshot) {
  return (
    countDataRows_(BILLS_SHEET) ===
      snapshot.bills.length &&

    countDataRows_(TEMPLATES_SHEET) ===
      snapshot.templates.length &&

    countDataRows_(PLANS_SHEET) ===
      snapshot.plans.length &&

    countDataRows_(PAYMENTS_SHEET) ===
      snapshot.payments.length &&

    countDataRows_(METHODS_SHEET) ===
      snapshot.paymentMethods.length &&

    countDataRows_(DELETED_SHEET) ===
      snapshot.tombstones.length &&

    countDataRows_(ACTIVITY_SHEET) ===
      activityRows_(
        snapshot.bills
      ).length
  );
}

function numberOrBlank_(value) {
  if (
    value === null ||
    value === undefined ||
    value === ''
  ) {
    return '';
  }

  const number = Number(value);

  return isNaN(number)
    ? ''
    : number;
}

function json_(value) {
  return ContentService
    .createTextOutput(
      JSON.stringify(value)
    )
    .setMimeType(
      ContentService.MimeType.JSON
    );
}

Part 2: Add other family devices

Connect another device

Open or install the same PayNexus family app on the second device.
Open Settings and select or create the correct Profile.
Enter the same Profile-specific /exec URL and private family connection code.
Tap Save & sync now before adding or changing bills.
Confirm the expected bills appear.

Using more than one Profile

Create the Profile in PayNexus.
Create a separate blank Google Sheet for that Profile.
Repeat the Apps Script setup using a new private family connection code.
Connect that Profile to its own URL and code.

Do not connect two different Profiles to the same Sheet; their records could merge.

Part 3: Turn on Advisor sharing

What the advisor can seeMonthly forecast totals, the amount spent in the same month last year, the Profile name you choose, update time, and alerts you send. The Advisor endpoint does not return payees, bill names, account references, notes, payment methods, or transactions.

Open Advisor sharing

In the family PayNexus app, open the Profile you want to share, tap the gear, and find Advisor sharing.

Name the Profile for your advisor

Turn on Allow an advisor to see this profile’s summary. Enter the name your advisor should see, such as Smith Family or Smith Household Account.

Generate the restricted advisor code

Tap Generate new code, then Copy code. This code begins with PNA-. It is different from the private family connection code stored in Code.gs.

Save and publish the summary

Tap Save settings. Then tap the header Sync button, or return to Settings and choose Save & sync now. The Advisor app cannot connect until this sync successfully stores the advisor permission and summary in the Sheet.

Share only the two advisor credentials

Give your advisor:

The Profile’s Apps Script Web App URL ending in /exec.
The restricted advisor code beginning with PNA-.

For better privacy, send the URL and advisor code in separate messages. Never send the private family connection code.

Part 4: Connect and use PayNexus Advisor

Add the client

Open PayNexus Advisor and tap Add client. Paste the client’s /exec URL and restricted PNA- advisor code.

Set this client’s individual thresholds

Choose values for Elevated above typical, High above typical, Minimum extra dollars, and the typical-month calculation. These settings apply only to this client. The 12-month median is the recommended starting point because one unusually expensive month has less influence on it.

Connect and review

Tap Connect client. The app tests the secure connection and opens the client summary. Review the current-month forecast, the amount spent in the same month last year, the next 90 days, the next 12 months, monthly status colors, and family alerts.

Use the portfolio

On Client overview, sort by needs attention, client name, or next-90-day cash need. Use Sync all to refresh every connected client. Open one client and use Refresh client for an individual update.

Adjust thresholds later

Open the client and choose Client thresholds. Change that client’s elevated percentage, high percentage, minimum extra dollars, or median/average method, then save. No other client’s thresholds change.

Send and receive an alert

In the family app, choose an alert reason, add an optional message without sensitive account information, tap Send alert with next sync, save, and sync. The advisor then refreshes that client or taps Sync all.

Revoke access when needed

In the family app, tap Revoke access, save settings, and sync. The old advisor code will stop working. To reconnect later, enable sharing, generate a new code, save, sync, and give the advisor the new code.

Add the apps to your device

If you are using a SwiftSpeed native wrapper, the app is already installed. These steps are for opening a hosted web version like an app.

iPhone or iPad

Open the family or Advisor app link in Safari.
Tap Share.
Choose Add to Home Screen.
Turn on Open as Web App if shown, then tap Add.

Android

Open the app link in Chrome.
Tap the three-dot menu.
Choose Install app or Add to Home screen.
Confirm the installation.

Mac

Open the app link in Safari.
Choose File → Add to Dock.
Name the app and click Add.

Windows

Open the app link in Microsoft Edge.
Choose … → Apps → Install this site as an app.
Click Install and choose any shortcut options.

Updating Code.gs later

Open the PayNexus Google Sheet.
Choose Extensions → Apps Script.
Replace the old Code.gs with the updated code, but put your own existing private family connection code back into PRIVATE_CONNECTION_CODE.
Save and run setupPayNexus.
Choose Deploy → Manage deployments.
Edit the existing deployment, choose New version, and deploy.

Updating the existing deployment normally keeps the same /exec URL. Creating an entirely new deployment may produce a new URL that must be updated in the family app and Advisor app.

Troubleshooting

Save & sync does not respond or no success message appears

Confirm both fields are filled in, the URL ends in /exec, and the family connection code exactly matches Code.gs. Wait for any current sync to finish, then tap Save & sync now again.

The app says the connection code is incorrect

The family app requires PRIVATE_CONNECTION_CODE from Code.gs, without quotation marks. The PNA- advisor code will not work in the family sync field.

The Advisor app says access is not authorized

In the family app, confirm Advisor sharing is enabled and the advisor code matches. Then save settings and complete a successful family sync before trying the Advisor app again.

The Advisor app connects but shows no monthly information

Open the family app and sync the correct Profile. The family app creates the advisor-safe monthly summary; the Advisor app reads that summary only after it has been synced to Google Sheets.

I changed Code.gs but nothing changed

Saving the script is not enough. Use Deploy → Manage deployments → Edit → New version → Deploy. Keep the existing deployment when you want to retain the same URL.

“Anyone” is not available during deployment

A work or school administrator may block public Apps Script web apps. Use a personal Google account or ask the administrator whether Apps Script web-app deployment is permitted.

A second device shows old or empty data

Confirm it is connected to the same Profile-specific URL and family connection code. Sync the device with the newest data first, wait for success, then sync the second device.

I need to remove an advisor from only one Profile

Open that Profile in the family app, revoke its advisor access, save, and sync. Other Profiles and advisor connections are unaffected.

Connect PayNexus to Google Sheets—and your advisor.

Before you begin

Part 1: Create the PayNexus Google Sheet

Create a blank Sheet

Open Apps Script

Copy the complete connector

Create your private family connection code

Run the one-time setup

Deploy the web app

Connect the family PayNexus app

Confirm the first sync

Complete PayNexus Code.gs

Part 2: Add other family devices

Connect another device

Using more than one Profile

Part 3: Turn on Advisor sharing

Open Advisor sharing

Name the Profile for your advisor

Generate the restricted advisor code

Save and publish the summary

Share only the two advisor credentials

Part 4: Connect and use PayNexus Advisor

Add the client

Set this client’s individual thresholds

Connect and review

Use the portfolio

Adjust thresholds later

Send and receive an alert

Revoke access when needed

Add the apps to your device

iPhone or iPad

Android

Mac

Windows

Updating Code.gs later

Troubleshooting

Rooted in Science, Guided by Energy—Healing the Mind & Body, One Step at a Time

Quick links

Subscribe