1

Here's what I'm trying to accomplish using the Excel Javascript API:

  1. I have a Sheet1 and Sheet2 in my workbook. Within Sheet2 there may be some references to cells in Sheet2, but I don't know where or even if they exist.
  2. I'd like to replace Sheet1 with a different sheet (say Sheet3), but I don't want to the references to cells to Sheet1 on Sheet2 to break.
  3. Both Sheet1 and Sheet2 have arbitrary amount of data on them, and have an unbounded amount as well.

My question: how can I perform the above operation, using only the Excel Javascript API, in an efficient way?

Here are a few solutions I've thought about (some I've tried):

  1. Delete Sheet1. Rename Sheet3 to Sheet1. This does not work, as it causes the references to Sheet1 in Sheet2 to become undefined.
  2. Find and replace all instances of Sheet1 with Sheet3 in Sheet2 before the delete and rename. I am concerned this will not be efficient enough as it must look through all cells.
  3. Pause calculation, then delete, then rename, then resume calculation. This does not work as pausing calculation apparently doesn't pause this kind of reference updating, and so the references in Sheet2 to Sheet1 still break.
  4. Copy all cell data from Sheet3 to Sheet1. However, as we would also need to copy formatting, which as far as I understand cannot be done in bulk. I don't think we could do this efficiently enough, as there may be an arbitrary amount of data.

Any feedback would be greatly appreciated. Without a solution, I fear it may tank a month or so of work on our add-in :(

Nate Rush
  • 360
  • 2
  • 14
  • It would be great if you could share more about your scenario before answering your question. do you think you could copy the content from sheet3 and overwrite the content in sheet1? – Raymond Lu Apr 18 '20 at 08:08
  • Thanks for the feedback. I've added more details to the scenario, as well as expanded on the solutions and why copying the data appears to not work. – Nate Rush Apr 18 '20 at 16:41

1 Answers1

0

You could use range.copyFrom API for option 4, with this API you can choose to copy formats, formulas, values. by default it copy all.

  await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sheet3");
    const sheet2 = context.workbook.worksheets.getItem("Sheet1");

    // copy a range from another sheet
    sheet2.getRange("A1").copyFrom("Sheet3!A1:E1");

    await context.sync();
  });

The document can be found at https://learn.microsoft.com/en-us/javascript/api/excel/excel.range?view=excel-js-preview#copyfrom-sourcerange--copytype--skipblanks--transpose-

Raymond Lu
  • 2,178
  • 1
  • 6
  • 19