CODEWITHSUNDEEP
office-jsexceljs
J. Lavoie
J. Lavoie

Reputation: 334

Set cell value with getCell() in table Excel JS

I'm stuck on this and can't find an answer on any website. I'm trying to set the value of the cell but I'm getting the following error:

The property "values" isn't loaded. Please call the load method and context.sync() ...

I do understand I need to call the load method but none of the loads I tried work.

    Excel.run(function (context) {
       var sheet = context.workbook.worksheets.getActiveWorksheet();
       var FilledTable = sheet.tables.getItem(randomId);
       FilledTable.rows.load("items"); //Tried this and many others...
       FilledTable.getDataBodyRange().load("values");

       return context.sync().then(function () {
          FilledTable.getRange().getCell(1,0).values = [[ "test" ]]; //Trying to do this! 
       });
    }

Thank you for any help !!

UPDATE

I'm trying to loop the first column of my table and set an hyperlink using the value already set.

Example: Complete example

    Excel.run(function (context) {
      var sheet = context.workbook.worksheets.getActiveWorksheet();
      var filledTable = sheet.tables.getItem(randomId);
      var usedRange = filledTable.getRange().load("values, rowCount");

      return context.sync().then(function () {
         var keyPos = headers.indexOf("Key");
         if(keyPos > -1){
            for(var z = 1; z < usedRange.rowCount; z++){
               usedRange.values[z][0].hyperlink = {
                  address: `url` + usedRange.values[z][0],
                  documentReference: null,
                  screenTip: null,
                  textToDisplay: usedRange.values[z][0],
                };
              }
           }
         });
       })

Upvotes: 2

Views: 10817

Answers (1)

J. Lavoie
J. Lavoie

Reputation: 334

I was able to fix my issue by using getRow and getColumn instead of values! Here's the fixed code:

Excel.run(function (context) {
  var sheet = context.workbook.worksheets.getActiveWorksheet();
  var filledTable = sheet.tables.getItem(randomId);
  var usedRange = filledTable.getRange().load("values, rowCount");

  return context.sync().then(function () {
    var keyPos = headers.indexOf("Key");
    if(keyPos > -1){
      for(var z = 1; z < usedRange.rowCount; z++){
        usedRange.getRow(z).getColumn(keyPos).hyperlink = {
          address: `url` + usedRange.values[z][keyPos],
          documentReference: null,
          screenTip: null,
          textToDisplay: usedRange.values[z][keyPos],
        };
      }
    }
  });
})

Upvotes: 2

Related Questions