Search code examples
google-apps-scriptgoogle-sheetsgoogle-sheets-apigoogle-sheets-macros

Range.copyTo(DestinationRange, .PASTE_NORMAL, false) failing to copy

I should start by apologizing if I fail to describe the problem I am facing and or the context in which I stumbled upon it. If there are any other pieces of information that might help you understand what is going on, please let me know.

The Context: I have two sheets in one Spreadsheet: "Checklist", which is supposed to work as a form and "Edit Checklist", which as the name implies will be used to edit the former's format. The idea is that once the user finishes editing a macro will then substitute the previous form by simply copying the new version onto the old one.

The Problem: Using the Range.copyTo() function via script won't copy the entirety of the new form, missing a whole column and especially the format and merged ranges. Please, find below screenshots

Example of a new form, i.e. yet to be copied. The result of using Range.copyTo() to copy the new form

Code

/**
 *   Updates the checklist form after edditing.
 */
function update_checklist(){

  // First we clean whatever used to be the checklis
  del_checklist_sections();

  // Now we gather the new checklist into a range
  new_checklist = get_edit_checklist_form_range();

  // We now have to make room for the new list on the Checklist Sheet
  b_row = spreadsheet.getRangeByName("Checklist!"+NAMED_RANGES.form_start).getRow();
  f_form.insertRowsAfter(b_row, new_checklist.getNumRows());

  //new_checklist.copyFormatToRange(f_form, 1, new_checklist.getLastColumn(), b_row+1, b_row+1+new_checklist.getNumRows());

  new_checklist.copyTo(get_checklist_form_range(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  //new_checklist.copyTo(get_checklist_form_range(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

}

/**
 *   Correctly grabs the sections and tasks range in the checklist sheet. 
 *
 * The correct range doesn't include the first and last rows of the named range. 
 *
 * @return {Range} the corresponding range.
 */
function get_checklist_form_range(){

  full_range = spreadsheet.getRangeByName("Checklist!"+NAMED_RANGES.full_form);
  if (full_range.getNumRows() > 2){

    // We are adding one so we won't consider the initial row in the range, and subtracting to so we won't consider the first and last.
    return f_form.getRange(full_range.getRow()+1, full_range.getColumn(), full_range.getNumRows() - 2);
  }else{
    return 0
  }

}

function get_edit_checklist_form_range(){

  full_range = spreadsheet.getRangeByName("Edit Checklist!"+NAMED_RANGES.full_form)
  // We are adding one so we won't consider the initial row in the range, and subtracting to so we won't consider the first and last.
  //  return f_form.getRange(full_range.getRow()+1, full_range.getColumn(), full_range.getNumRows() - 2)
  return e_form.getRange(full_range.getRow()+1, full_range.getColumn(), full_range.getNumRows() - 2);

}

Note: Trying to make it easier to find the ranges, I used named ranges to specify parts of the sheets.

Updates:

  1. I tried to record a macro to do the copy and paste task and using the generated code works just fine. I compared my piece of code with the one automatically generated and the only difference is the way the ranges are written, but in the end, they should be the same.
  2. I tried pasting the format first and then the values; it didn't work.
Solution

  • I took me a while to come back to this project, but here we go:

    The problem was how I was acquiring the range. It turns out I was not selecting all the data I wanted to copy and paste.