Copy/Paste Values and Formatting of Visible Range Script about office-scripts-docs-reference

[AlexJerabek]

Hi @tabrewer,

Thanks for making this request. I have a few questions and suggestions.

1. Were you able to find the sample on the ExcelScript.RangeCopyType page? I believe that is a good starting place for your sample. The Range.copyFrom method will be the best way to accomplish your scenario.
2. In the line let visibleRange = `firstSheet.getRange("A1:V200").getVisibleView();` - you should remove the backticks (`). That is assigning the string "firstSheet.getRange [...]" to visibleRange.
3. To copy both formatting and values, change ExcelScript.RangeCopyType.formats to ExcelScript.RangeCopyType.all.

[tabrewer]

@AlexJerabek ,

I have seen the ExcelScript.RangeCopyType page. In fact, I just went ahead and copied that code directly into my workbook and changes from CopyType.formats to CopyType.all and it worked wonderfully. My next challenge is to only copy and paste formats and values of visible cells. I cannot seem to get the syntax correct for this.

I have managed to copy visible cell values with the following script:

function main(workbook: ExcelScript.Workbook) {
  let firstSheet = workbook.getWorksheet("Sheet1");
  let visibleRange = `firstSheet.getRange("A1:V200").getVisibleView();`
  let copyFormatting = ExcelScript.RangeCopyType.formats;
  let visibleRangeValues = visibleRange.getValues();
  let sheetToPaste = workbook.getWorksheet("Sheet2");
  let pastedValues = sheetToPaste.getRangeByIndexes(0, 0,
    visibleRange.getRowCount(), visibleRange.getColumnCount());
  pastedValues.setValues(visibleRangeValues);

}

When I try to adapt the ExcelScript.RangeCopyType script for Visible cell I get some errors in the formula. I think it is likely my limited understanding of TypeScript. But I hope you can help.

function main(workbook: ExcelScript.Workbook) {
  //Define the first worksheet.
  let firstSheet = workbook.getWorksheet("Sheet1");

  //Define Paste Sheet
  let sheetToPaste = workbook.getWorksheet("Sheet2");

  //Get Visible Range
  let visibleRange = firstSheet.getRange("A1:V200").getVisibleView();

  // Get the used range on the current worksheet.
  let usedRange = visibleRange

  // Copy the formats from the used range to the new worksheet.
  let copyType = ExcelScript.RangeCopyType.all //* Change this to copy different information, such as formats. */
  let targetRange = sheetToPaste.getRangeByIndexes(
    usedRange.getRowIndex(),
    usedRange.getColumnIndex(),
    usedRange.getRowCount(),
    usedRange.getColumnCount());
  targetRange.copyFrom(usedRange, copyType);
}

[tabrewer]

Here are the errors I am getting with the above code.
[17, 15] Property 'getRowIndex' does not exist on type 'RangeView'.
[18, 15] Property 'getColumnIndex' does not exist on type 'RangeView'.
[21, 24] Argument of type 'RangeView' is not assignable to parameter of type 'string | Range | RangeAreas'.
Type 'RangeView' is missing the following properties from type 'Range': getAddress, getAddressLocal, getCellCount, getColumnHidden, and 97 more.

[tabrewer]

Ideally, my code would be able to iterate through every worksheet with a power automate trigger.

[AlexJerabek]

Hi @tabrewer,

I think the issue is that RangeView is not a Range. You'll need to call RangeView.getRange to get something that can be copied.

Try the following script for your scenario. It assumes you have a table on Sheet1 with a filter already applied and that Sheet2 exists and is empty.

function main(workbook: ExcelScript.Workbook) {
  // Get the filtered data from Sheet1.
  const currentSheet = workbook.getWorksheet("Sheet1");
  const table = currentSheet.getTables()[0];
  const source = table.getRange().getVisibleView().getRange()
  
  // Copy the data into the other sheet.
  const otherSheet = workbook.getWorksheet("Sheet2");
  const otherRangeCorner = otherSheet.getRange("A1");
  otherRangeCorner.copyFrom(source, ExcelScript.RangeCopyType.all);
}

One other thing to note about the script is that it uses Range.copyFrom on a single-cell Range. This allows the pasted Range to expand as needed. I chose A1, but you could use information from the starting Range if you wanted.

Please let me know if this helps. If so, I'll add it as a sample in the RangeView interface.

[tabrewer]

I took the above code and adapted it a bit and it creates a perfect copy of the sheet!

function main(workbook: ExcelScript.Workbook) {
  // Get the filtered data from Sheet1.
  const currentSheet = workbook.getWorksheet("Sheet1");
  const source = currentSheet.getRange().getVisibleView().getRange()

  // Copy the data into the other sheet.
  const otherSheet = workbook.getWorksheet("Sheet2");
  const otherRangeCorner = otherSheet.getRange("A1");
  otherRangeCorner.copyFrom(source, ExcelScript.RangeCopyType.all);
}

I wish I could have my data in tables, but I am working with about 40 custom built contract documents and need to replicate the entire contract without formulas and paste only visible values and all formatting.

The functionality I am looking for deletes the filtered cells and pastes only the remaining values and formatting (As it does when you use "Go To Special" to copy visible cells and paste in excel desktop. As the formula is now, it keeps the filtered cells when pasting. Any thoughts on a solution for that?

[AlexJerabek]

Is it possible for you to provide a sample workbook for me to test with? It would be easier for me to see how the filters are applied and what the script is copying.

This issue has been automatically marked as stale because it is marked as needing author feedback but has not had any activity for 4 days. It will be closed if no further activity occurs within 3 days of this comment. Thank you for your interest in Office Add-ins!

This issue has been closed due to inactivity. Please comment if you still need assistance and we'll re-open the issue.