GOOGLE SHEETS: Using Macro to archive Cell Range to second sheet sometimes loses Range Value and results in Reference Error

Question

brand new user coming in peace. Joined just now to hopefully get some help with some Macros I have set up. Apologies if I use any incorrect terminology or language.

The strange thing is that in some instances, it works fine, and in others it replaces the "range" portion of a formula with a "reference error".

For example, I have a series of templates that has merged cells that pulls a Product Name from a cell, searches for an img link and displays it. Like so...

Template Image

The formula in the merged cells reads: =iferror(IMAGE(vlookup(C5,ImageList!A3:E,5,0))," ")

• C5 is Product Name,
• ImageList is another sheet with all image linking.

The macro I have setup is a simple Selection, Copy, and Paste to an "Archive" Sheet, so i can quickly generate multiple order requests, have each moved to a working sheet, then clears and replaces the template from a Clean Template Archive in another sheet.

The macro for that is:

function ArchiveAndReset() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('B4:r30').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Existing Product Order Archive'), true);
  spreadsheet.getRange('3:33').activate();
  spreadsheet.getActiveSheet().insertRowsBefore(spreadsheet.getActiveRange().getRow(), 30);
  spreadsheet.getActiveRange().offset(0, 0, 30, spreadsheet.getActiveRange().getNumColumns()).activate();
  spreadsheet.getRange('B4').activate();
  spreadsheet.getRange('Existing Product Template!B4:r30').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Existing Product Order Archive'), true);
  spreadsheet.getRange('c4').activate();
  spreadsheet.getRange('Existing Product Template!c4').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Clean Template Archive'), true);
  spreadsheet.getRange('B4:r30').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Existing Product Template'), true);
  spreadsheet.getRange('B4').activate();
  spreadsheet.getRange('\'Clean Template Archive\'!B4:r30').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
}

The location of the merged image cells fall inside the 'B4:r30' range. Specifically "K4:M14".

This particular macro is working fine, and when copied to the Archive, the image formula is preserved as it was : =iferror(IMAGE(vlookup(C5,ImageList!A3:E,5,0))," ")

The problem I'm having is this. I have a second template setup in the same fashion, but when the copy to the archive is completed the formula appears as such: =iferror(IMAGE(vlookup(C5,#REF,5,0))," ") losing the Range portion of the formula.

When I examine the macro for that, I can not find any difference that would result in this issue. That macro is as follows:

function KIDArchiveReset() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('42:73').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Existing Product Order Archive'), true);
  spreadsheet.getRange('4:34').activate();
  spreadsheet.getActiveSheet().insertRowsBefore(spreadsheet.getActiveRange().getRow(), 34);
  spreadsheet.getActiveRange().offset(0, 0, 31, spreadsheet.getActiveRange().getNumColumns()).activate();
  spreadsheet.getRange('5:5').activate();
  spreadsheet.getRange('\'Existing Product Template\'!42:73').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Clean Template Archive'), true);
  spreadsheet.getRange('73:104').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Existing Product Template'), true);
  spreadsheet.getRange('42:42').activate();
  spreadsheet.getRange('\'Clean Template Archive\'!73:104').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
  spreadsheet.getRange('42:42').activate();
  spreadsheet.getRange('\'Clean Template Archive\'!73:104').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Existing Product Order Archive'), true);
}

To my eyes, it is and operates in the exact same way. The merged cells again appear inside the first selection range of "('42:73')". Specifically "K42:M52". It uses the same "PASTE_NORMAL" function.

I have tried various methods to try to get them to act the same way, including...

•breaking the merge, copying the single cell, reforming the merge... still results in reference error

•pasting Formula Only, same error.

•pasting Value Only, same error.

A co-worker has suggested the issue may be something about using Absolute vs. Relative values in the macro, but I'm not sure how to test/fix that and as stated the first Template works fine. I believe I used Absolute reference values when I set up each template, and they otherwise operate as expected.

Thank you very much for your time and consideration.

Answer 1

I made a simple test case to show how I copied the content of one sheet containing an image Test to another Archive.

As Cooper mentioned you should learn to use the SpreadsheetApp Object Model to do your scripting rather than using the Record Macro. Record Macro tracks your mouse and keyboard entries and produces a bunch of Activate instructions that aren't needed and often redundant.

Sheet named Test

Sheet named ImageList

Sheet named Archive after copy

Code.gs

function test() {
  try {
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spread.getSheetByName("Test");
    let original = sheet.getRange("A1:D14");
    sheet = spread.getSheetByName("Archive");
    let copy = sheet.getRange("A1:D14")
    original.copyTo(copy);
  }
  catch(err) {
    console.log("Error in test: "+err)
  }
}

References

• SpreadsheetApp.getActiveSpreadsheet()
• Spreadsheet.getSheetByName()
• Sheet.getRange()
• Range.copyTo()