0

I want to link an application to validate updates made in Excel. I am trying to use Glue42 interop methods. Does anyone have a code sample?

I have successfully linked two apps, but not excel

I change a cell in excel and my external app validates this can be changed.

Community
  • 1
  • 1
jofordy
  • 1
  • 2

1 Answers1

0

I believe you missed the Glue for Office documentation. There are two sections you can go to from the home page: one for general programming and one for the Office connectors.

Anyways, up to the question, you need first to obtain a reference to a Glue4Office object with code, similar to the one below:

const config = {
    // ...,
    excel: true // enable Excel integration
}

Glue4Office(config)
    .then(g4o => {
       const excel = g4o.excel
       // interact with Excel
    })
    .catch(console.error)

Then, once you open a sheet and get a reference to it, you can subscribe to its onChanged event, where you can call the errorCallback argument. Here is an example:

excel.openSheet(config)
    .then(sheet => {
            sheet.onChanged((data, errorCallback, doneCallback) => {
                // process changes here
                // errorCallback(...) - call if there are validation errors
                // doneCallback() - call if not doing validations or everything's OK
            })
    })

sheet.onChanged((data, errorCallback, doneCallback) => {
    // ...
    const errors = []
    data.reduce(
        (errors, rowData, rowIndex) => {
            if (!rowData['firstName']) {
                errors.push({
                    row: rowIndex + 1,
                    column: 'firstName',
                    description: 'First name is mandatory'
                })
            }
            if (Number(rowData['subscriptionMonths']) < 6) {
                errors.push({
                    row: rowIndex + 1,
                    column: 1, // <- note column index this time
                    description: 'Subscription period must be at least 6 months',
                    text: '6' // <- replacing what the user typed
                })
            }
        },
        [])

    // if during the validation pass we've accumulated any errors 
    // we need to call the errorCallback, otherwise the doneCallback
    if (errors.length > 0) {
        errorCallback(errors)
    }
    else {
        doneCallback()
    }
})

There are convenient declarative approaches as well.

erjitka
  • 115
  • 1
  • 9