Forum Discussion
Need a script to find the last cell-copy, paste as value, then format as text on another sheet.
Here is a greatly simplified version of a script I am trying to run:
In this example I have it going to a single column sheet that will work fine. It may work better (I need this for a Power Automate process) if we can have it go to a specified last row with data, last cell in a table - but I am imagining that will be significantly more complicated (and perhaps unecessary)
Perhaps like
function main(workbook: ExcelScript.Workbook) { const value = workbook .getWorksheet("Sheet1") .getUsedRange() .getLastCell() .getValue() .toString() const targetCell = workbook .getWorksheet("Sheet2") .getUsedRange() .getLastCell() .getOffsetRange(1, 0) targetCell .setNumberFormats([["@"]]) targetCell .setValue(value) }
6 Replies
If to copy the range that could be
function main(workbook: ExcelScript.Workbook) { const values = workbook .getWorksheet("Sheet1") .getUsedRange() .getLastColumn() .getValues() .slice(1) .map(x => Array.of(x[0].toString())) const cells = values.length workbook .getWorksheet("Sheet2") .getRange("A1") .getOffsetRange(1,0) .getResizedRange(cells-1,0) .setValues(values) }In simplest case that is
function main(workbook: ExcelScript.Workbook) { const value = workbook .getWorksheet("Sheet1") .getUsedRange() .getLastCell() .getValue() .toString() workbook .getWorksheet("Sheet2") .getUsedRange() .getLastCell() .getOffsetRange(1,0) .setValue(value) }if you mean to copy last cell from column E in Sheet1 to the bottom of values in column A in Sheet2.
Or you mean to copy all values from first sheet into second one at once?
This first one is preferred (and almost accomplishes everything). Is there a way to add that the final formatting be text? It is coming out "general" and I need it to read as text for some other functionality.
Perhaps like
function main(workbook: ExcelScript.Workbook) { const value = workbook .getWorksheet("Sheet1") .getUsedRange() .getLastCell() .getValue() .toString() const targetCell = workbook .getWorksheet("Sheet2") .getUsedRange() .getLastCell() .getOffsetRange(1, 0) targetCell .setNumberFormats([["@"]]) targetCell .setValue(value) }
