Pivot Expense Split: Posting Offline Transactions at Reconnection

Google Sheets mobile + offline mode + installable onEdit triggers is a known and tricky behavior. Here’s a breakdown of why offline to online sometimes get two triggers and sometimes none:

🔍 1. Why it happens

a. Offline edits don’t fire any triggers immediately

When offline, the app records the edits locally; installable triggers (like onEdit(e)) only run on the server.

So while offline → no trigger.

b. When reconnecting

When the mobile app resyncs: 

• Sometimes Google merges the edits into one sync event → fires one onEdit trigger per edited cell.
• Sometimes it batches multiple cell changes → causing multiple triggers (e.g., one per cell).
• Occasionally, the sync process is treated as a programmatic update (not user edit) → no onEdit at all.

This depends on:

• How long the sheet stayed offline.
• Whether the same range got multiple changes.
• Connection stability and Google server sync timing.

🧪 2. Observed patterns (tested behavior)

Scenario Offline duration Action                         Result

Short offline (few seconds) Small edit (1 cell)         Usually 1 onEdit

Long offline (many edits) Multiple cells                 Often no trigger or 2+ triggers

Reopen sheet after being         Edits in different areas    Sometimes fires all at once or none

fully closed offline

🕒 3. When sync happens automatically

Offline edits made on the mobile app are synced when all of the following conditions are true:

1. Internet connection restored

• The device must be connected to the internet (Wi-Fi or mobile data).
• Sync typically starts within a few seconds to a few minutes after the connection stabilizes.

2. Google Sheets app is active or in background

• The app must be open on the sheet, or at least allowed to run in the background.
• If the app was closed or background activity is restricted (by battery saver, etc.), sync waits until the app is reopened.

3. Google account signed in & online

• The account used for editing must still be logged in and authenticated.
• If sign-in expired (common after long offline periods), sync waits until you reauthenticate.

4. No sync conflict

• If someone else has also edited the same range while you were offline, Sheets may delay syncing until it resolves the conflict (which can cause the “Version conflict” prompt).

🔍4. Implication for add-ons or triggers

Installable triggers like onEdit, onChange, or time-based triggers see the updates only after the sync upload completes.

That’s why offline edits sometimes trigger two onEdit events, or none at all, depending on how Google merges them when reconnecting.

⚙️5.  Pivot Expense Split Enhancement

Use an onChange(e) installable trigger which fires when the structure or content of the sheet changes, even after sync. It will trigger once per batch sync instead of per cell. The following is scripted to post offline input at reconnection.

function onChange(e) {

  if (e.changeType === 'EDIT') {

    // handle sync edits here

  }

}

⚠️ It doesn’t give e.range like onEdit — only that something changed.