cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

How to get last row dynamically using office script

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

 

data-01.PNG

 

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
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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

 

View solution in original post

6 REPLIES 6
Highlighted
Helper II
Helper II

Hi everyone, 

 

Any suggestion on how to resolve this?

Highlighted

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

 

View solution in original post

Highlighted

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

Highlighted

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

Highlighted

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

Highlighted

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 

muges01_1-1604261005930.png

 

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.

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Users online (8,624)