Skip to main content
Question

Google Sheet Connector - Moves Manual Content

  • August 6, 2026
  • 1 reply
  • 12 views
C
Apprentice Author

Hi everyone,

I’m running into an issue with the Pigment Google Sheets Connector shifting existing formulas in adjacent columns during data syncs.

The Setup:

  • I configured the Pigment Connector to start exporting data at cell B1 (leaving Column A open).

  • Column A contains custom formulas designed to calculate alongside the imported data.

The Issue:

  • Every time the connector runs, it pushes the formulas in Column A down beneath the bottom of the imported dataset, rather than keeping them aligned in rows 1 onwards alongside the newly exported data.

Expected vs. Actual:

  • Expected: Data populates starting at B1, while Column A maintains its formulas side-by-side with the imported rows.

  • Actual: The connector appears to insert/clear full sheet rows, bumping all existing Column A formulas below the last row of the export.

1 reply

Veniamin
Apprentice Helper
Forum|alt.badge.img
  • VeniaminApprentice Helper
  • Apprentice Helper
  • August 8, 2026

Hi Charlie,

The target cell only tells the connector where the top-left of the block goes. It doesn't reserve the rest of the sheet for you. On refresh the connector resizes the sheet to match the row count of the View, and the row operations drag column A along with them.

This isn't new. The same thing was reported in 2022 by someone trying to load into a fixed template, and the answer from Pigment was to split it across two tabs:
https://community.pigment.com/questions-conversations-40/import-from-pigment-to-google-sheet-without-creating-new-lines-and-columns-732

Three years on, that's still the pattern I'd use. Concretely:

Give the connector a tab of its own. Call it `Pigment_Raw`, target A1, and put nothing else on it.

Do the work on a second tab, with formulas that size themselves to the data instead of being filled down. Say your raw block has the key in column A and the numbers you need in C and D. On the calc tab:


A2:  =ARRAYFORMULA(IF(Pigment_Raw!A2:A="","", Pigment_Raw!D2:D - Pigment_Raw!C2:C))
B1:  =ARRAYFORMULA(IF(Pigment_Raw!A1:Z="","", Pigment_Raw!A1:Z))
 

B1 mirrors the whole imported block, so you get the side-by-side layout you were after, and A2 is one formula that grows and shrinks with the export. Nothing to fill down, and it survives a change in row count. The `IF` wrapper is only there to stop empty source cells rendering as zeros.

One thing that will bite you later whichever layout you land on: try not to rely on row position at all. If a new item appears in the middle of the dimension, everything below it shifts by one, and a positional formula will calculate against the wrong record without throwing an error. If your column A calculation belongs to a specific item rather than to "whatever is on this row", key it with XLOOKUP on the item and let the position float.

The better option, if it fits: can that column A logic be a Metric in Pigment? If yes, put it there and add it to the View. It then arrives as another exported column, there's no Sheets formula left to protect, and everyone pulling that View gets the same number. Do it on a saved View rather than the default one, since a refresh picks up whatever the View looks like at that moment.

And if none of this works because the output genuinely has to land inside a fixed template, the connector is the wrong tool for that last step. The Export API plus a short Apps Script writing with `setValues()` into a fixed range will overwrite in place and never insert a row. More setup, but total control over the layout.

Connection setup docs, for reference: https://kb.pigment.com/docs/load-data-gsheets

Ask your question here!

Terms & ConditionsAccessibility statement