QBO Task user-defined fields in Excel

Question

I am using pulling existing Task data into an Excel spreadsheet to use with a Score. I have an Excel named range executing ?=ImportForm/ListDeep?TargetObject=Loan&TargetObjectID={LoanID}.

My tasks include several user-defined fields, but the data renders in Excel lumps them together in a single column called XmlData. How can I use these values in Excel formulas?

Answer 1

The ImportForm/ExecuteFlat method will normalize user-defined fields (that that, elements stored in ImportForm.XmlData. ExecuteFlat requires an Operation parameter, which it will execute before normalizing the results.

For your example, call:

?=ImportForm/ExecuteFlat?Operation=ListDeep&TargetObject=Loan&TargetObjectID={LoanID}

More generically, assume that ImportForm/Search?ID=1 returns:

<ImportFormItem>
  <ImportFormID>1</ImportFormID>
  ...
  <XmlData>
    <ImportFormXml>
      <Foo>Bar</Foo>
      <Hello>World</Hello>
    </ImportFormXml>
  </XmlData>
</ImportFormItem>

Calling ImportForm/ExecuteFlat?Operation=Search?ID=1 would return:

<ImportFormItem>
  <ImportFormID>1</ImportFormID>
  ...
  <Foo>Bar</Foo>
  <Hello>World</Hello>
</ImportFormItem>