How to update formulas in a Sheet when copying reference sheet into current spreadsheet

1
votes

I have two Spreadsheets, A & B, and I am writing a script to merge them with customizations for each Business Unit.

Spreadsheet A contains a sheet "Calculations", with formula references to a sheet called "Materials", but it does not contain the Materials sheet.

I wrote the script to copy the "Materials" sheet from Spreadsheet B into Spreadsheet A. 

var materialsSheet = spreadSheetB.getSheetByName("Materials");
materialsSheet.copyTo(spreadSheetA).setName("Materials");

However, the formulas on "Calculations" sheet in SpreadsheetA are still complaining "Materials" sheet not found. If I manually go into the Front End and simulate an edit, the values in the cells get updated.

I tried copying the formulas and pasting them back via Apps-Script, but it didn't work. Any suggestions on how to make it work?

var readRange = calcSheet.getRange(9,1,7,6);
readRange.copyTo(calcSheet.getRange(9,1,7,6));

Another line of thought: My guess is that "Materials" sheet is referenced internally by G-sheets using some reference id, not its name. How do I programmatically update the internal reference?

Update: My question was identified as a possible duplicate of another question, which uses "Indirect" in its formula. I don't use "Indirect" in my spreadsheet, which takes in the address as a String and returns a reference address. That is not the functionality I am looking for, so I cannot add it to my sheet formulas. I did try the suggested solution for that question (use IFERROR), but it didn't help either. I just get the null value returned in case of error.

1
google-apps-scriptgoogle-sheets
When you say "If I manually go into the Front End and simulate an edit, the values in the cells get updated", do you mean that after an edit, the references in your formulas are able to find the copied sheet but until then they cannot? – Argyll
@Argyll Your understanding is correct. The formula needs to be fake-edited on the front-end to make it work. Copy/pasting the formula back in the sheet using Google Apps-Script does not have the same effect. – J. Doe
Have you tried flush()? – Argyll
I didn't try flush(), but I manually reloaded the spreadsheet several times , but it didn't have any impact. I am guessing manual reload does more things than flush(), so I would have seen it then, right? – J. Doe
@J.Doe: I am not sure if that's the case. Manual reload is opening the sheet again. Some updates may be triggered by edit. Try flush and see if it works? – Argyll

1 Answers

1
votes

How about this workaround? Although I'm not sure whether this is useful for your situation, can you try this? The flow of this workaround is as follows.

Flow :

  1. Copy "Materials" to destination sheet.
  2. Copy "Calculations" to destination sheet as the name of "Calculations_temp".
  3. Copy data of "Calculations_temp" to "Calculations".
  4. Delete "Calculations_temp".

Confirmations :

  • In my environment, I confirmed that SpreadsheetApp.flush() cannot be used for this situation.
  • In your question, for I tried copying the formulas and pasting them back via Apps-Script, but it didn't work.
    • It confirmed that the formulas don't work, even if it copies the range of same sheet.

So by overwriting the sheet of "Calculations" using "Calculations_temp" which was copied, I tried to update the formulas.

Sample script :

var srcId = "#####"; // File ID of "Materials"
var dstId = "#####"; // File ID of "Calculations"
var src = SpreadsheetApp.openById(srcId).getSheetByName("Materials");
var dstSheet = SpreadsheetApp.openById(dstId);

// Copy "Materials" to destination sheet.
src.copyTo(dstSheet).setName("Materials");

// Copy "Calculations" to destination sheet as the name of "Calculations_temp".
dstSheet.getSheetByName("Calculations").copyTo(dstSheet).setName("Calculations_temp");

// Copy data of "Calculations_temp" to "Calculations".
//  var range = dstSheet.getSheetByName("Calculations").getDataRange(); // I confirmed that this didn't work.
var range = dstSheet.getSheetByName("Calculations_temp").getDataRange();
range.copyTo(dstSheet.getSheetByName("Calculations").getDataRange());

// Delete "Calculations_temp".
dstSheet.deleteSheet(dstSheet.getSheetByName("Calculations_temp"));

Note :

  • When you use this, please input the file IDs of "Materials" and "Calculations".

If this was not useful for you, I'm sorry.