CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Paul
Paul

Reputation: 1

How to SetValues to a range with a different dimension

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.")
}

Upvotes: 0

Views: 1413

Answers (2)

Paul
Paul

Reputation: 1

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.")

}

Upvotes: 0

Wicket
Wicket

Reputation: 38160

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

Related

Upvotes: 0

Related Questions