0

I am using an importrange query from other sheet to filter data: https://docs.google.com/spreadsheets/d/1FbeoKhiJ3jqYXqfIgAK_tHXPZ_Z1Xrm33ulu9gomXuE/edit?usp=sharing

The "colors" sheet has data populated from query ("items" sheet) and users are allowed to write comments on each row.

Problem: if row order is changed on "items" sheet, the "colors" sheet row order will be lost and any previous comment will not match the correct row.

How can I prevent this? Is there a way to keep paired query row results and comments? Maybe using timestamp as a reference?

At the moment sheet "colors" contains a correct pairing, once a row is inserted between "items", the pairing is lost

1

Suggested setup

Use two sheets, Import and Entry

  1. Import sheet

    • Use to import the data to your spreadsheet,
      • Use IMPORTRANGE on this sheet
  2. Entry sheet

"FAQ"

  • What will happen if a row is deleted from the source sheet (the one that was imported)?
    • The cells on the Entry Sheet that use lookup will show #N/A but the key/id and the columns that hold the comments will remain in place.
  • Getting closer to the solution. One more question: there should be a way to keep the IDs updated in both "tables", otherwise manual update is required vanishing benefits. For instance, I could use a cell with timestamp in the "import sheet", BUT this should be stored somehow in the "entry sheet" when a comment is inserted.... – Riccardo Jun 7 '18 at 6:01
  • I could run a script that will copy the timestamp from "import sheet" to "entry sheet" each time a new comment is created – Riccardo Jun 7 '18 at 8:47
  • @Riccardo If you don't want to manually update the keys/ids between sheets, definitely you should use something that use Google Apps Script or Google Sheets API. – Rubén Jun 7 '18 at 12:33
  • @Riccardo I added a link to a related thread that I found by searching "two way sync" (I posted an answer to it some time ago) – Rubén Jun 7 '18 at 12:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.