Hide row based on value -short story on how Office scripts make simple things difficult

(0) Share

Report

Posted on by piotrostr

Hi, i try to record office script to filter out or hide rows based on the cell value.

Its really should be an easy task to achieve yet im trying to sort it for 3rd day now.

I need to have script which is going to different tabs to check if there is "0" or "1" in column "T".

If there is 0 -> hide the row
If there is 1 -> unhide the row

It could be filtering out, grouping or whatever which will make it gone from the list, i cant use vba cause its online based file,

the very same area will be filled in by user and the whole idea od the script would be to show only values for this week (column "t" checks if the data falls into range and set 0 if not and 1 if yes).
If i use fixed filter it wont change when the date will set new 0s and 1s
cant use filter formulas or power query because there is already plenty of tabs and i don't want to create more with only desired output.

i have column "T" containing formula resulting in 0 or 1, id like to run this daily and hide everything containing 0, and leave everything with 1 visible.

I recorded script and later used chatgpt to write the code:

function main(workbook: ExcelScript.Workbook) { let sheets = workbook.getWorksheets(); sheets.forEach(sheet => { let selectedSheet = workbook.getActiveWorksheet(); // Get the data from range T7:T5000 (headers) let rowData: string[] = selectedSheet.getRange("T:T").getValues()[0] ?? selectedSheet.getRange("T:T").getValues() .catch(error => { throw new Error("The range T7:T5000 is empty."); }); // Check if rowData is not empty if (rowData.length !== 0) { // Loop through the rowData to hide/show columns based on cell values for (let i = 0; i < rowData.length; i++) { if (rowData[i] === 0) { selectedSheet.getRangeByIndexes(0, i + 29, rowCount, 1).setRowHidden(true); } else if (rowData[i] === 1) { selectedSheet.getRangeByIndexes(0, i + 29, rowCount, 1).setRowHidden(false); } } } }); }

it results in error:

Line 7: Cannot read properties of null (reading '0')

Can't really understand how it can result in errors if i record this script with built in tools? (it doesn't matter if i record or ask any llm, the error is same or similar or says functions doesn't exist)

Thanks in advance

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (0)

MichelleRanMSFT Microsoft Employee on at

Like (0)

Report
Hi @piotrostr, thanks for your question - is the code you shared the exact script that was generated by the recorder, without any further modifications? If so, could you share the sequence of actions you took to generate that script? It appears to have several errors and we can check if there might be a problem with the recorder output.

In terms of a script to achieve the scenario you described, you can try this:
function main(workbook: ExcelScript.Workbook) { let sheets = workbook.getWorksheets(); sheets.forEach(sheet => { let column = sheet.getRange('T7:T5000'); let values = column.getValues(); for (let rowIndex = 0; rowIndex < values.length; rowIndex++) { if (values[rowIndex][0] == 0) { column.getRow(rowIndex).setRowHidden(true); } else if (values[rowIndex][0] == 1) { column.getRow(rowIndex).setRowHidden(false); } } }); }
Also, one thing to be aware of when using ChatGPT or other LLMs is that they don't always understand the API and may create code with errors.

1 people found this reply helpful.

Was this reply helpful? Yes No
JAYPARK80 2 on at

Like (0)

Report

@MichelleRanMSFT
Thanks for the script! I had a question though, if we wanted this to only affect a specific sheet instead of all sheets, how would we go about doing that?

edit: nevermind, figured it out

Was this reply helpful? Yes No
CU26092045-0 2 on at

Like (0)

Report
@MichelleRanMSFT thank you for your contribution to the original question from piotrostr - I found it through much searching for something very similar, except that I need this to work by hiding columns, not rows. My situation is slightly different (a bit more elaborate than simply 0 or 1) but in essence if I can get the code working in the same way for columns then I think I can adapt it, beyond a proof of concept, to my actual situation.

I copied your code into Excel and (in addition to changing the range reference from 'T7:T5000' to 'A1:A5' to test the hide rows version) I then changed all references to row to column, and all references to column to row, (as well as changing the range reference to 'A1: E1') thinking that would adjust the functionality, but whereas your hide rows variant works perfectly, my hide columns version to hide columns will only act upon the first column, A.

Here's my adaptation of your hide rows code:

function main(workbook: ExcelScript.Workbook) { let sheets = workbook.getWorksheets(); sheets.forEach(sheet => { let column = sheet.getRange('A1:A5'); console.log (column) let values = column.getValues(); console.log (values) for (let rowIndex = 0; rowIndex < values.length; rowIndex++) { if (values[rowIndex][0] == 0) { column.getRow(rowIndex).setRowHidden(true); } else if (values[rowIndex][0] == 1) { column.getRow(rowIndex).setRowHidden(false); } } }); }

And here's my further adaptation in an attempt change it to hide columns:

function main(workbook: ExcelScript.Workbook) { let sheets = workbook.getWorksheets(); sheets.forEach(sheet => { let row = sheet.getRange('A1:E1'); console.log (row) let values = row.getValues(); console.log (values) for (let columnIndex = 0; columnIndex < values.length; columnIndex++) { if (values[columnIndex][0] == 0) { row.getColumn(columnIndex).setColumnHidden(true); } else if (values[columnIndex][0] == 1) { row.getColumn(columnIndex).setColumnHidden(false); } } }); }

As you'll see, I added some debugging (console.log) output to help me see where things were going wrong and I noticed something quite fundamentally different between the hide rows vs hide columns variants, in terms of the debugging output of console.log (values).

The hide rows version produces this:

Whereas my 'hide columns' version produces this:

So this corroborates the difference in script behaviour (and specifically that only the first column is being acted upon in my hide columns version) but I don't know why, or how to resolve it! The only thought I have is that it might be to do with the way array dimensions are determined from rows versus columns, for example as a row (A1:E1 , or A1,B1,C1,D1,E1) versus a column {A1:A5 , or A1;A2;A3;A4;A5) and this in turn affecting the loop.

Might you able to help, please?

I'd be most grateful - thank you,

John

Was this reply helpful? Yes No