Question:
How to type the values of an object into a spreadsheet in TypeScript?

Problem

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?


Solution

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 the 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);

}


Suggested blogs:

>Type key of the record in a self-referential manner in TypeScript?

>How to get the last cell with data for a given column in TypeScript?

>Ignore requests by interceptors based on request content in TypeScript?

>Create data with Typescript Sequelize model without passing in an id?

>How to delete duplicate names from an Array in Typescript?

>How to Select checkboxes on an HTML treeview with JavaScript?

>How to use querySelectorAll()" with multiple conditions in JavaScript?

>How to fix the mouseover event glitch in JavaScript?

>How to do light and dark mode in a website using HTML and JavaScript?


Nisha Patel

Nisha Patel

Submit
0 Answers