Answered

Get Last Line with Office Scripts

(0) Share

Report

Posted on by victor_lion

I have a .xlsx with a lot of rows of information, I wonder how can I get the last line of this document?

I'm supposed to be adding more lines to the end of the archive, every month. There is a sample of the script I'm working on:

function main(workbook: ExcelScript.Workbook) {
 let selectedSheet = workbook.getWorksheet("sheet");
 return JSON.stringify(selectedSheet.getRange());
}

Also tried with:

return JSON.stringify(selectedSheet.getRange().getVisibleView().getValues());

But I'm getting the error:

Converting circular structure to JSON
--> starting at object with constructor 't'
| property 'm_trackedObjects' -> object with constructor 'e'
--- property 'm_context' closes the circle
clientRequestId: 986aa897-d022-43a5-b32e-672524deb8c8

Maybe you can help me with this, thanks!

Categories:

Using flows

All responses (6)

Answers (1)

Sort by

DamoBird365 8,942 Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @victor_lion

You could probably get away without using Office Scripts and do something like https://www.exceltip.com/lookup-formulas/find-the-last-row-of-data-in-excel.html
victor_lion 67 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi, I'm looking to a function like .getUsedRange(), as I am no trying to add more content to the file.
Yutao Huang Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

Hey @victor_lion ,

Wondering if you have tried Range.getSurroundingRegion( ) . I think that should work as long as there are no completely blank rows or blank columns within the range.

https://docs.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.range?view=office-scripts#getSurroundingRegion__

-Yutao
victor_lion 67 on at

Like
a
(0)

Report
Copy link

Link copied!
Thank you @Yutao I've solved this problem using the following script:
let columnValues = selectedSheet.getUsedRange(true).getColumn(column).getValues(); return columnValues;

And by this I get an array, so by this I know the length of the file, and can get the last line.
Verified answer

Yutao Huang Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

@victor_lion - it's great to hear you've found the solution! Wondering if you could also try selectedSheet.getUsedRange(true).getRowCount(), which might be a bit more efficient.
victor_lion 67 on at

Like
a
(0)

Report
Copy link

Link copied!

Yes @Yutao it works! Thanks!