How to get last row dynamically using office script

(0) Share

Report

Posted on by 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

Categories:

General topics

I have the same question (0)

All responses (10)

Answers (1)

muges01 65 on at

Like (0)

Report

Hi everyone,

Any suggestion on how to resolve this?

Was this reply helpful? Yes No
Verified answer

Yutao Huang Microsoft Employee on at

Like (2)

Report
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

Was this reply helpful? Yes No
muges01 65 on at

Like (0)

Report

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

Was this reply helpful? Yes No
muges01 65 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Yutao Huang Microsoft Employee on at

Like (0)

Report

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

Was this reply helpful? Yes No
muges01 65 on at

Like (1)

Report
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.

Was this reply helpful? Yes No
Yutao Huang Microsoft Employee on at

Like (0)

Report
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

Was this reply helpful? Yes No
MichaelAngeloP 6 on at

Like (0)

Report

const lastrow = ws.getUsedRange(true).getLastRow().getRowIndex();
true to get cells with values only

Was this reply helpful? Yes No
MichaelAngeloP 6 on at

Like (0)

Report

const lastrow = ws.getUsedRange(true).getLastRow().getRowIndex();
true to get cells with values only

Was this reply helpful? Yes No
Yutao Huang Microsoft Employee on at

Like (0)

Report

@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.

Was this reply helpful? Yes No