Type the values of an object into a spreadsheet

Question

Using the script below, I'm trying to enter into cells A1 to C3, the following values:

This is the script:

function main(workbook: ExcelScript.Workbook, jsonData?: fluxos[]) {
  if (jsonData == undefined || jsonData == null) {       
    console.log("jsonData empty. Using dummy data.");     
    jsonData = dummyData;                                 
  };

  let ws = workbook.getActiveWorksheet();

  ws.getRange("A1").setValues(jsonData);
   
  
};

/*----------------------- Object Interface ---------------------------*/
interface fluxos {
  Fluxo1: string,
  Fluxo2: string,
  Fluxo3: string
}
/*----------------------- Dummy Data ---------------------------*/
const dummyData: fluxos[] = [
  {
    Fluxo1: "Atividades operacionais",
    Fluxo2: "Recebimentos de clientes",
    Fluxo3: "Cliente A"
  },
  {
    Fluxo1: "Atividades operacionais",
    Fluxo2: "Recebimentos de clientes",
    Fluxo3: "Cliente B"
  },
  {
    Fluxo1: "Atividades operacionais",
    Fluxo2: "Pagamentos a fornecedores",
    Fluxo3: "Fornecedor A"
  }
];

The problem is on row:

ws.getRange("A1").setValues(jsonData);

How to output the values of the object jsonData into the cells?

Answer 1

Convert dummyData into a 2D array before writing data to cells.

function main(workbook: ExcelScript.Workbook, jsonData?: fluxos[]) {
    if (jsonData == undefined || jsonData == null) {
        console.log("jsonData empty. Using dummy data.");
        jsonData = dummyData;
    }

    let ws = workbook.getActiveWorksheet();

    let dataValues: string[][] = jsonData.map(item => [item.Fluxo1, item.Fluxo2, item.Fluxo3]);

    ws.getRange("A1:C3").setValues(dataValues);
}

---

If you do not like hardcode key name (eg. item.Fluxo1), below is a dynamic approach.

function main(workbook: ExcelScript.Workbook, jsonData?: fluxos[]) {
    if (jsonData == undefined || jsonData == null) {
        console.log("jsonData empty. Using dummy data.");
        jsonData = dummyData;
    }
    let ws = workbook.getActiveWorksheet();
    let firstItem = jsonData[0];
    let propertyNames = Object.keys(firstItem);
    let dataValues: string[][] = jsonData.map(item => {
        return propertyNames.map(propertyName => item[propertyName]);
    });
    let colCount = propertyNames.length;
    let rowCount = jsonData.length;
    ws.getRangeByIndexes(0, 0, rowCount, colCount).setValues(dataValues);
}