Set dropdown value removes dropdown list in Spreadsheet

[LaurensSequesto]

When we fill in values in a Spreadsheet and the cell has a dropdown list, the dropdown list is gone. This is annoying as our customer wants to see which options were available as well. We extracted the options from the sheet exactly how they are configured, without any changes.

We use ApiRange.SetValue to insert the value of the customer into the cell, which works but like said above the dropdown list is gone. See images attached:

How the cell looks like before being filled in:

How the cell looks like after it is filled:

How it should look like when filled in with the SetValue:

Also note that before filled in - the cell has a bold formatting and this is also not been taken over…

Are we doing something wrong or is this a bug?

Kind Regards

[Alexandre]

Hello @LaurensSequesto
Please share a test file and specify how exactly you’re using ApiRange.SetValue to insert the value into the cell, we will check the situation.

[LaurensSequesto]

Method we call:

const setCellValue = (address: string, html: string) => {
  // @ts-ignore
  Asc.scope.address = address
  // @ts-ignore
  window.connector.callCommand(
    () => {
      // @ts-ignore
      const sheet = Api.GetActiveSheet()
      // @ts-ignore
      const range = sheet.GetRange(Asc.scope.address)
      // Then select this range
      range.Select()
    },
    () => {
      // @ts-ignore
      window.connector.executeMethod('PasteHtml', [html])
    }
  )
}

html here:
<p>1 - Oui</p>

So the issue we have is that if the cell has some layout (border, font size, font family, etc…) it isn’t taken over and it’s completely lost which is too bad as it screws up the spreadsheet of our customers.

As you can see in this screenshot:

(first row is with our script, the row below is when we select an option from the dropdown)

Test file.xlsx (21.4 KB)

[Alexandre]

Hello @LaurensSequesto
Thank you for the file. Indeed, I was able to reproduce the situation using the pasteHTML method.
I might have missed something, but why do you want to use HTML here?
I adapted the provided code to achieve the same scenario without HTML, and it seems OK.

const setDropdownValue = (address, value) => {
  // @ts-ignore
  Asc.scope.address = address
  // @ts-ignore
  Asc.scope.value = value

  // @ts-ignore
  window.connector.callCommand(
    () => {
      // @ts-ignore
      const sheet = Api.GetActiveSheet()
      // @ts-ignore
      const range = sheet.GetRange(Asc.scope.address)
      range.SetValue(Asc.scope.value)
    },
    true
  )
}
setDropdownValue("G4", "1 - Oui")

If I misunderstood the request and you need to paste HTML into the dropdown menu, please confirm that.

[LaurensSequesto]

Thanks for looking into this @Alexandre.
It indeed might look strange to use PasteHTML for this, but all our editors are using HTML.
And that’s why we use the PasteHTML method as we don’t know which answer we are working with (a simple dropdown option or rather styled piece of text).

Is there a way to keep the styling of the cell when pasting as HTML?

Kind Regards,
Laurens

[Alexandre]

Understood, we are checking the situation. I will contact you as soon as possible.

[LaurensSequesto]

Hi @Alexandre,

Do you have an update for us?

Thanks in advance!

Kind regards

[Constantine]

Hello @LaurensSequesto

We are still checking out the situation. You will be informed once any news becomes available.