How to get last row dynamically using office script

[muges01]

Hi everyone, 

How can i get last row dynamically using office script.

I'm running the office script automatically on the file added to SharePoint library.

The example of file

The excel file might content some text and some table data and some text below the table data. The script will remove the top text and then add table to the table data. How can i find the last row of the table data?

let table1 = workbook.addTable(selectedSheet.getRange("C3:F6"), true);

The value of F6 should be dynamically set. Example it could be F10.

The goal is to extract the table data from the uploaded excel file then insert them into SharePoint list.

Thank you

[muges01]

Hi everyone, 

Any suggestion on how to resolve this?

[Yutao · Accepted solution]

Hey @muges01 ,

I'm thinking you probably could do something like this:

// Assuming startCell is the top-left cell of the source range
let table1 = workbook.addTable(startCell.getSurroundingRegion(), true);

This should work as long as the source range (that you want to convert into a table) doesn't contain any completely blank rows or columns..

Hope this helps!

Yutao

[muges01]

Hi @Yutao ,

Sorry for the late reply, i'm not sure how did i missed this response.  I'll check this and get back to you.

Regards

Muges

[muges01]

Hi @Yutao 

Thank you that work but I also return the column if it contain space. Is there a way to ignore any column which has empty space column?

Thank you

[Yutao]

Hey @muges01 ,

Is it possible to share a simple example of that scenario (where you want to ignore empty columns)? Maybe a screenshot of the worksheet or something like that?

Thanks!

Yutao

[muges01]

Hi @Yutao ,

Thank you for your response. If you like I can send a sample workbook, but I couldn't find a way attach a document here.

Anyway, here is the screenshot 

Example script 

let worksheet = workbook.getActiveWorksheet();
let rows = worksheet.getRange('B5').getSurroundingRegion();

console.log(rows.getValues());

It should be getting from B5 to C9 instead it will be getting A5 to C9 because some of the cells in column has empty space.

Please let me know, if you need any other information.

[Yutao]

Hello @muges01 ,

Unfortunately, I'm not sure if there is a way to ignore cells with only whitespaces when retrieving surrounding region.

Is it ok to modify the content of the workbook? If yes, maybe you can try to "purge" those cells that contain only whitespaces. Here is one example:

let table1 = workbook.getActiveWorksheet().getRange("B5").getSurroundingRegion();
let values = table1.getValues();
console.log(values);

let newValues = values.map(row => row.map(cell => cell.toString().trim()));
table1.setValues(newValues);

let table2 = workbook.getActiveWorksheet().getRange("B5").getSurroundingRegion();

console.log(table2.getValues());

Although this is probably not the best solution as I fear it might alter the number/text formats. But it might be fine if you only care about the extracted text values.

Hope this helps!

Yutao

[MichaelAngeloP]

const lastrow = ws.getUsedRange(true).getLastRow().getRowIndex();

true to get cells with values only

[MichaelAngeloP]

const lastrow = ws.getUsedRange(true).getLastRow().getRowIndex();

true to get cells with values only

[Yutao]

@MichaelAngeloP - thanks for sharing this tip! I think it's especially useful when we want to exclude cells that don't contain anything but have non-default format like background-color/border/style/etc.

If I understand it correctly, @muges01 's case is a bit tricky here since some of those cells are actually not empty but containing SPACE characters. So `getUsedRange(true)` won't exclude them.