1

I create Developer Metadata for each of the columns in the sheet. If a new column gets created, I track it and create another developer metadata for it.

The process above works great until the user starts to move columns using Cut (cmd+x) and Paste (cmd+v). When you cut and paste, the developer metadata is transferred to the destination column and as a result, you're ending with 2 metadata on the same column. It gets more complicated when you do that process multiple times.

Eventually, I collect the changes and I see more than 1 metadata on a given column and I don't know which of them to choose.

Do you have an Idea how can I deal with that scenario?

The flow explained:

  1. The user connect his google sheet document.
  2. I go over his sheet and create metadata on the columns.
Name [444] id [689] Country [997]
Du 10 US
Re 30 US
  1. The user is doing multiple changes on the sheet. One of the changes is cutting and pasting the column country over id. As a result, the column id gets removed but the metadata id we created stays on (Google Sheet API implementation)

Here is the new state:

Name [444] Country [689, 997]
Du US
Re US

As you can see now, we have 2 metadata ids on the same column (Country). Why it is a problem for me? when I periodically collect the changes I recollect the metadata changes from the column. When I encounter two metadata ids on the same column I don't know which of them to choose. So why can't I just select randomly? because I already have an existing mapping on my end and I don't know which of them to choose now. Take into account that the user may have changed the column name also so I can count on the column label.

TheMaster
  • 45,448
  • 6
  • 62
  • 85
Aviv Kebab
  • 21
  • 3
  • Can you create a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example) that is the smallest script that produces the problem and share it? This makes it easier to find where the error is. – Emel Nov 29 '21 at 14:12
  • Thanks Emel. I've added an explanation of the flow. – Aviv Kebab Nov 30 '21 at 15:15
  • To control this type of problem you should use a Trigger of type [`onEdit(e)`](https://developers.google.com/apps-script/guides/triggers#onedite) to check what column is being cutted and delete the appropriate Metadata. Check the [Google Sheets events](https://developers.google.com/apps-script/guides/triggers/events#google_sheets_events) to now how to interact with them inside Google Apps Script. **Tip**: Using `Logger.log(JSON.stringify(e))` always help. Are you using Google Apps Script? – Emel Nov 30 '21 at 16:15
  • What would be your suggested solution then? There is no immutable point to fix your metadata. No implementation even on Google's side will fix this. There is no logical solution here. – TheMaster Nov 30 '21 at 17:41
  • @Emel You're right. I can use Google Apps Script in order to obtain the change. Using it will require modifying our customers' sheets and we avoid it. Correct me if I'm wrong but once you create the script, the user can access it through the UI - what would happen if he deletes it?. – Aviv Kebab Dec 01 '21 at 14:27
  • Hi, @AvivKebab, as you say, it would be a [container-bound](https://developers.google.com/apps-script/guides/bound) script and it would be accesible from the Sheets. As @TheMaster says, the implementation is complicated, since you would have to handle more variables than the `onEdit()` event offers... Would you have the possibility to add more control to the metadata? Adding another value that serves as a key, maybe? – Emel Dec 03 '21 at 08:39
  • Yes. In addition to the ID property, you also have a value property to which you assign free text. Do you have an idea how can I use it as part of the solution? – Aviv Kebab Dec 05 '21 at 13:25
  • If you had another value that is assigned to the metadata, you could control which column has been copied, right? Columns should have a corresponding metadata, and if they don't, then it has been copied. That is to say (`A->Name[44]`, if `A->id[689],Name[44]`, you should delete the one that doesn't correspond to A. But following this logic, you can do it only with one value, knowing which value corresponds to which column from the beginning, you could establish a record of what has been copied to where, right? – Emel Dec 06 '21 at 08:20

0 Answers0