CODEWITHSUNDEEP
javascriptexceloffice-js
g00golplex
g00golplex

Reputation: 427

excel-JS excel left() worksheet function not working

When I am trying to use the Excel left() worksheet function in visual studio Excel-JS api, it does not work at all! this means there is no error message, there is no value and not a clue what is not working I am talking about this statement:

  onzin = ctx.workbook.functions.left(Acode.values.toString(), 7);
  onzin.load("values","address");

According to the (incredibly incomplete) documentation from Microsoft https://learn.microsoft.com/en-us/javascript/api/excel/excel.worksheetcollection?view=office-js you have to load the variable in the Excel Object model before you use it and after a synchronization of the context, the values are available. But in my 2nd promise I loaded the values and the address of "onzin" but when I want to assign the values to the ICcode Range, it does not do it and furthermore, when I put a breakpoint in it, it seams to be empty and no errors.

Here is my code:

Excel.run(function (ctx) {
//always use the Data sheet
    var MyDataSheet = ctx.workbook.worksheets.getItem("Data");
    var ConfigSheet = ctx.workbook.worksheets.getItem("Config");
    var onzin;
    MyDataSheet.activate();
    var productStartRange = MyDataSheet.getUsedRange();
    //define a range before the values can be loaded to the Excel Object model
    var Acode = MyDataSheet.getRange("A3").load("values, address");
productStartRange.load("values, address, length");                                                                
// Run the queued-up commands, and return a promise to indicate task completion                       
return ctx.sync()
    .then(function () {
        var myBounds = GetBounds(productStartRange);                                
        ConfigSheet.activate();

        //put the column and row bounds in the config sheet                            
        var ColBounds = ConfigSheet.getRange("B22");
        var RowBounds = ConfigSheet.getRange("B21");
        var NumProducts = ConfigSheet.getRange("B34");
        var NumProperties = ConfigSheet.getRange("B27");

        //ICcode.values = ctx.workbook.functions.left(Acode.values.toString(), 7);                                
        onzin = ctx.workbook.functions.left(Acode.values.toString(), 7);
        onzin.load("values","address");

        //ICcode.values = Acode.values.toString().substring(0, 7);
        //ICcode.values = onzin.values;
        ColBounds.values = myBounds.LastCol;
        RowBounds.values = myBounds.LastRow - 1;

        //total number of products
        NumProducts.values = RowBounds.values - 2;
        //total number of properties                                
        NumProperties.values = ColBounds.values - 2;

        //load the products  from the Data source sheet into one range
        var ProductRange = MyDataSheet.getRangeByIndexes(3, 1, myBounds.LastRow, 3);
        ProductRange.load("values");

    })
    .then(ctx.sync)
    .then(function () {
        var ICcode = ConfigSheet.getRange("B36");
        ICcode = onzin.values;

        //var Mystring = rowAddress.address;
        showNotification("onzin waardes: ", onzin.values);
        var PropSheet = ctx.workbook.worksheets.getItem("PropertySelection");
        PropSheet.activate();

    }); 


}).catch(errorHandler);

I would expect that the worksheet function takes the first 7 characters from the cell value of "Acode" and writes it to the range ICcode on location B36.

any help would be appreciated

Upvotes: 0

Views: 273

Answers (2)

Rick Kirkham
Rick Kirkham

Reputation: 9769

Deleted my earlier answer because I misread part of your code. This line of your code has two problems:

onzin.load("values","address");
  1. There should not be an "s" on the first string. It is just "value". Also, remove the "s" from the line ICcode = onzin.values;.
  2. There is no "address" property on onzin object. (The Excel.Range object does have properties named values and address, which is why I thought in my original answer that you were treating onzin as an Excel.Range object.)

I was able to spot the errors by reading this article in the official documentation: Call built-in Excel worksheet functions. This article is the very first search result in both Bing and Google if you search for "office add-ins worksheet functions". So, I gently don't agree with you that the documentation is "incredibly incomplete".

Upvotes: 1

philip yu
philip yu

Reputation: 1

Will you try this piece, it works on my side.

var Acode = sheet.getRange("B2").load("values, address");
await context.sync();
console.log(Acode);
var onzin = context.workbook.functions.left(Acode.values.toString(), 3);
var substring = Acode.values.toString().substring(0,7);
console.log(Acode.values.toString());
console.log(substring);

Upvotes: 0

Related Questions