Forum Discussion
Copy & Paste Based On Header
Under the column "Property Name" do you mean column in structured table or the text on the top of some range in the sheet? If structured table is it its name predefined; if range in which row(s) of the sheet such text (considering as the header) could appear?
You copy everything in such column till end, but paste into another sheet only special values. What are such special values, what is the logic to select them?
Above probably means we paste only values without formatting properties, correct?
When paste shall we overwrite existing values in target range (and perhaps clean it before since from run to run size could be different) or we append pasted values to the end?
- Hi i have one main spreadsheet which isn't in a table it has multiple 'Headers'/text. I want to populate another sheet using the data under this header. Sometimes there will be formulas in the master file hence the paste values.
That could be
function main(workbook: ExcelScript.Workbook) { const header = "Property Name" const source = workbook .getWorksheet("M") .findAll(header, { completeMatch: true }) .getAreas()[0] .getExtendedRange(ExcelScript.KeyboardDirection.down) const target = workbook .getWorksheet("P") .findAll(header, { completeMatch: true }) .getAreas()[0] .getExtendedRange(ExcelScript.KeyboardDirection.down) target.clear() target.copyFrom(source, ExcelScript.RangeCopyType.values) }We find first appeared in each sheet cell with "Property Name" text in it. In source sheet get range till first empty cell in the column down. If you assume that could be blank cells within the range we need to use another logic.
In the target sheet we clear all cells in the range found by similar way as in source, and paste here values from the source.
Copy/pasting includes header. If header in target has some formatting and you'd like to keep it we need to exclude first row from source/target ranges (e.g. with getOffsetRange ).
