Hello,

I am trying to create a table containing 82 columns and 10000 rows (11028 to be exact) in a sheet.

I tried to add the lines one by one and then to do a Sync but the table is not created.

I thought it was a lot of data at one time so I tried adding lines in packets of 1000. This time, I get the table but only the first 1000 rows are filled.

In the log, I see all the packets go by.

 

In the code below :

- "values" is the json formatted data i try to add 

- "tableName" speaks for itself.

The steps before 5 are inscriptions of some data in other sheets but these works.

 

function CreateODataQuerySheetStep5_List(values, tableName) {
    let n = values.length;
    if (n > 0) {

        CreateODataQuerySheetStep5_ListTableHeader(values, tableName);      
    } else {
        Excel.run(function (context) {
            let headers = [["No data"]];
            let sheet = context.workbook.worksheets.getItem(newQueryName);
            let table = sheet.tables.add("A1", true);
            table.name = tableName;
            table.getHeaderRowRange().values = headers;
            sheet.activate();
            window.location.href = "Home.html";
            return context.sync();
        }).catch(function (error) {
            logging(sessionStorage.getItem("Token"), error);
        });        
    }
}

function CreateODataQuerySheetStep5_ListTableHeader(values, tableName)
{
    console.log("Creating header");
    Excel.run(function (context) {

        console.log("Get Sheet");
        let sheet = context.workbook.worksheets.getItem(newQueryName);

        console.log("Define table horizontal length");
        let line = values[0];
        let keys = Object.keys(line);
        let address = "A1:" + ColumnToLetter(keys.length) + "1";

        console.log("Computing header columns");
        let headers = [];
        for (let j = 0; j < keys.length; j++) {
            let key = keys[j];
            headers.push(key);
        }

        console.log("Adding table");
        let table = sheet.tables.add(address, true);
        table.name = tableName;
        let temp = [];
        temp.push(headers);

        console.log("Putting header");
        table.getHeaderRowRange().values = temp;


        return context.sync().then(function () {

            console.log("Header created")
            CreateODataQuerySheetStep5_List1000(values, 0, tableName, headers);

        }).catch(function (error) {
            logging(sessionStorage.getItem("Token"), error);
        });

    }).catch(function (error) {
        logging(sessionStorage.getItem("Token"), error);
    });
}

function CreateODataQuerySheetStep5_List1000(values, start, tableName, headers)
{
    console.log("Adding 1000 rows");
    Excel.run(function (context) {
        console.log("Get Sheet");
        let sheet = context.workbook.worksheets.getItem(newQueryName);

        console.log("Get Table");
        let table = sheet.tables.getItem(tableName);

        console.log("Computing max between 1000 and remaining lines");
        let x = values.length - start;
        let n = Math.min(1000, x);

        console.log("Adding rows from " + start.toString() + " to " + (start + n).toString());
        for (let i = start; i < n; i++) {
            let line = values[i];
            let keys = Object.keys(line);

            let row = [];
            for (let jj = 0; jj < headers.length; jj++) {
                let prop = headers[jj];
                let value = line[prop];

                if ($.type(value).toLowerCase() === 'string') {
                    value = "'" + value;
                }

                row.push(value);
            }
            table.rows.add(null, [row]);
        }
        console.log("Rows added");
    
        let y = start + 1000;
        if (y < values.length)
        {
            console.log("Rows are remaining");
            return context.sync().then(function () {

                console.log("Recursive Call");
                CreateODataQuerySheetStep5_List1000(values, y, tableName, headers);

            }).catch(function (error) {
                logging(sessionStorage.getItem("Token"), error);
            });
        }
        else
        {
            //if (Office.context.requirements.isSetSupported("ExcelApi", 1.2)) {
            //    sheet.getUsedRange().format.autofitColumns();
            //    sheet.getUsedRange().format.autofitRows();
            //}
            console.log("Sheet activation");
            sheet.activate();

            return context.sync().then(function () {
                console.log("return to home");
                window.location.href = "Home.html";
            });
        }
        

    }).catch(function (error) {
        logging(sessionStorage.getItem("Token"), error);
    });
    
}