How to SetValues to a range with a different dimension

Question

I need to prepare an empty sheet to be the size of a SetValues.

Details

I'm trying to use this nice routine from clueless to make a values-only copy of a google sheet file including all tabs. (Ultimately I'll run it on a schedule and download it to microsoft cloud drive so I can access live from Power BI). Clueless uses hard-coded parameters TL and BR to represent the Top Left and Bottom Right of the ranges. Clueless starts by deleting any tab with a matching name in the destination sheet, thus starting with a new tab.

Unfortunately, my source sheet tabs' ranges are not all consistent. Some larger than TL BL and some smaller. Therefore I get either "...out of bounds..." errors or "The number of rows in the data does not match the number of rows in the range. " if I increase or decrease the TL and BL globals.

How to I solve this? Is there maybe a way to prepare the destination sheet, before SetValues, by making it's size match the source sheet GetDataRange size before SetValues is executed?

Thanks!

    function update_view(dup_id="<spreadsheet_id>", TL="A1", BR="BE900") {
    // Open current Sheet
    var ss = SpreadsheetApp.getActiveSpreadsheet()
    // Supply a duplicate google doc ID. This document will be exported to excel
    var ds = SpreadsheetApp.openById(dup_id)
    // UI element for notifying in the google sheets
    var ui = SpreadsheetApp.getUi()
    //Copy each sheet one by one
    var sheets = ss.getSheets();
    for (i=0; i<sheets.length; i++) {
        src_sheet = sheets[i];
        sheet_name = src_sheet.getName();
        Logger.log(sheet_name);
        // If same sheet exists in the destination delete it and create an empty one
        dst_sheet = ds.getSheetByName(sheet_name);
        if (dst_sheet != null) {
            ds.deleteSheet(dst_sheet)
        }
        dst_sheet = ds.insertSheet(sheet_name);
        //set column width correctly
        for(j=1; j<=src_sheet.getLastColumn(); j++){
            dst_sheet.setColumnWidth(j, src_sheet.getColumnWidth(j))
        }
        src_range = src_sheet.getRange(TL + ":" + BR);
        dst_range = dst_sheet.getRange(TL + ":" + BR);
        //Note: DisplayValues is set as Values, formulas are removed in dup sheet
        dst_range.setValues(src_range.getDisplayValues());
        //Nice to haves for formatting
        dst_range.setFontColors(src_range.getFontColors());
        dst_range.setFontStyles(src_range.getFontStyles());
        dst_range.setBackgrounds(src_range.getBackgrounds());
        dst_range.setHorizontalAlignments(src_range.getHorizontalAlignments());
        dst_range.setVerticalAlignments(src_range.getVerticalAlignments());
        dst_range.setWraps(src_range.getWraps());

        dst_contents_range = dst_sheet.getDataRange();
        dst_contents_range.setBorder(true, true, true, true, true, true);
    }
    //Completed copy, Now open the dup document and export.
    ui.alert("Backup Complete, Please open " + dup_id + " sheet to view contents.")
}

score 0 · Answer 1 · answered Feb 20 '21 at 22:26

Instead of using A1 notation to get the range you might use getDataRange() to grab the data range (the all the rows and columns having at least one cell with a value), then might use getNumRows(), getNumColumns() among other methods or use Array.prototype.length() to get the range dimensions and grab a range of the same size from the destination sheet.

Resources

Getting "Those rows are out of bounds" error in Google script

score 0 · Answer 2 · answered Feb 22 '21 at 20:18

Thanks @Ruben. That was helpful. using getNumRows() and getNumColumns() did it. For others here is the complete function. I also changed the name and replaced my file id with

function MakeCopyAsValues(dup_id="<google file id>") {
// Open current Sheet
var ss = SpreadsheetApp.getActiveSpreadsheet()
// Supply a duplicate google doc ID.
var ds = SpreadsheetApp.openById(dup_id)
// UI element for notifying in the google sheets
var ui = SpreadsheetApp.getUi() 
//Copy each sheet one by one
var sheets = ss.getSheets();
for (i=0; i<sheets.length; i++) {
    src_sheet = sheets[i];
    sheet_name = src_sheet.getName();
    // Logger.log(sheet_name);  // this was helpful for debugging.
    // If same sheet exists in the destination delete it and create an empty one
    dst_sheet = ds.getSheetByName(sheet_name);
    if (dst_sheet != null) {
        ds.deleteSheet(dst_sheet)
    }
    dst_sheet = ds.insertSheet(sheet_name);

    src_range = src_sheet.getDataRange();  //Get the source's range that has data.
    dst_range = dst_sheet.getRange(1,1,src_range.getNumRows(), src_range.getNumColumns());

    //Note: DisplayValues is set as Values, formulas are removed in dup sheet
    dst_range.setValues(src_range.getDisplayValues());
    
    //set column widths correctly.  Moving this after the range setting prevents some errors.
    for(j=1; j<=src_sheet.getLastColumn(); j++){
        dst_sheet.setColumnWidth(j, src_sheet.getColumnWidth(j))
    }
    //Other Nice to haves for formatting
    dst_range.setFontColors(src_range.getFontColors());
    dst_range.setFontStyles(src_range.getFontStyles());
    dst_range.setBackgrounds(src_range.getBackgrounds());
    dst_range.setHorizontalAlignments(src_range.getHorizontalAlignments());
    dst_range.setVerticalAlignments(src_range.getVerticalAlignments());
    dst_range.setWraps(src_range.getWraps());

    dst_contents_range = dst_sheet.getDataRange();
    dst_contents_range.setBorder(true, true, true, true, true, true);
}
ui.alert("Backup Complete, Please open " + dup_id + " sheet to view contents.")

}

How to SetValues to a range with a different dimension

2 Answers2