Hiding columns using office scripts

Question

I would like to hide certain columns using office script for Excel Online workbook. I used the recorder to create the script. But when I ran it, it resulted in errors. Below is the recorded script:

function main(workbook: ExcelScript.Workbook) {
    let lockbox = workbook.getTable("Lockbox");
    // Set visibility of column(s) at range D:P on lockbox to true
    lockbox.getRange("D:P").setColumnHidden(true);
    // Set visibility of column(s) at range R:V on lockbox to true
    lockbox.getRange("R:V").setColumnHidden(true);
    // Set visibility of column(s) at range AB:AB on lockbox to true
    lockbox.getRange("AB:AB").setColumnHidden(true);
    // Set visibility of column(s) at range AE:AO on lockbox to true
    lockbox.getRange("AE:AO").setColumnHidden(true);
}

The Problems are below, but don't understand why it's not working:

[4, 19] Expected 0 arguments, but got 1.
[6, 19] Expected 0 arguments, but got 1.
[8, 19] Expected 0 arguments, but got 1.
[10, 19] Expected 0 arguments, but got 1.

Brian Gonzalez · Accepted Answer · 2023-01-12T01:30:27.807

For Excel Tables, getRange() works a bit differently than it does for the worksheet. Unlike the worksheet, getRange() for the Table accepts zero arguments. It's a function call that returns the range associated with the table. You can get the range associated with a column if you use the getColumn() function. From what I can tell, none of the column functions for Excel Tables support column letters or a range of column letters.

The easiest fix is to use the worksheet that contains the table. Once you have that, you can just update your code that hides the columns to use the worksheet instead of the lockbox. After you do that, the code should work as expected. You can see an example of how you might do that here:

    function main(workbook: ExcelScript.Workbook) {
      let lockbox = workbook.getTable("Lockbox");
      let ws: ExcelScript.Worksheet = lockbox.getWorksheet()
      // Set visibility of column(s) at range D:P on lockbox to true
      ws.getRange("D:P").setColumnHidden(true);
      // Set visibility of column(s) at range R:V on lockbox to true
      ws.getRange("R:V").setColumnHidden(true);
      // Set visibility of column(s) at range AB:AB on lockbox to true
      ws.getRange("AB:AB").setColumnHidden(true);
      // Set visibility of column(s) at range AE:AO on lockbox to true
      ws.getRange("AE:AO").setColumnHidden(true);
    }

You can also consolidate the lines to hide the columns into one line like so:

    function main(workbook: ExcelScript.Workbook) {
      let lockbox = workbook.getTable("Lockbox");
      let ws: ExcelScript.Worksheet = lockbox.getWorksheet()
      ws.getRanges("A:A,R:V,AB:AB,AE:AO").getAreas().forEach(cols=>cols.setColumnHidden(true));
    }

Hiding columns using office scripts

1 Answers1