CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
user6283187
user6283187

Reputation:

Google script - Why copyTo in for-loop doesn't work?

I'm trying to do copy-paste (only values) in some dynamic parts of my spreadsheet in order to get it ready to be exported to excel. I'm trying to use the copyTo method using an array and a nested for-loop.

// here i define all the sheets

var activeSS, mikumimSheet,rankSheet, conveSheet, srcSheet, clkImpSheet, gaSheet, salesSheet, lndpageSheet;

activeSS = SpreadsheetApp.getActiveSpreadsheet();
mikumimSheet = activeSS.getSheetByName('עותק של מיקומים בגוגל');
rankSheet = activeSS.getSheetByName("מיקומים בגוגל");
conveSheet = activeSS.getSheetByName("המרות");
srcSheet = activeSS.getSheetByName("מקורות תנועה");
clkImpSheet = activeSS.getSheetByName("חשיפות והקלקות");
gaSheet = activeSS.getSheetByName("Google Ads");
salesSheet = activeSS.getSheetByName("מכירות באתר");
lndpageSheet = activeSS.getSheetByName("דפי נחיתה");

// here some code inject data to cells all over the spread sheet

// setHeaders();

// here i store all the cells i need to copy paste and run the copyTo method

function copyPasteReplace() {
    var cells = [
        [srcSheet, "A2:G2", "B53:C53"],
        [salesSheet, "A1:F1"],
        [clkImpSheet, "A1:E1"],
        [rankSheet, "A1:E1", "C11:E11"],
        [conveSheet, "A1:G1", "A30:H30", "O11:O17"],
        [lndpageSheet, "A1:D1"],
        [gaSheet, "A1:F1"]
    ];

    for (var i = 0; i < cells.length; i++) {
        for (var n = 1; n < cells[i].length; n++) {
            cells[i][0].getRange(cells[i][n]).copyTo(cells[i][0].getRange(cells[i][n]), {
                contentsOnly: true
            });
        }
    }
}

// this is the order of execution

function onOpennn() {
    setHeaders();
    copyPasteReplace();
}

I expected all the values in the sheets first get injected and then pasted in value form (without formulas). What actualy happens in that the cells just get emptied.

Upvotes: 2

Views: 221

Answers (1)

user6283187
user6283187

Reputation:

I found the problem with my code. It turns out, that i had to add the sheet name in the ranges array, like so:

// instead of:

var cells = [
        [srcSheet, "A2:G2", "B53:C53"],
        [salesSheet, "A1:F1"],
        [clkImpSheet, "A1:E1"],
        [rankSheet, "A1:E1", "C11:E11"],
        [conveSheet, "A1:G1", "A30:H30", "O11:O17"],
        [lndpageSheet, "A1:D1"],
        [gaSheet, "A1:F1"]
    ];

// It should have been:

var cells = [
        [srcSheet, 'מקורות תנועה!A2:G2', 'מקורות תנועה!B53:C53'],
        [salesSheet, 'מכירות באתר!A1:F1'],
        [clkImpSheet, 'חשיפות והקלקות!A1:E1'],
        [rankSheet, 'מיקומים בגוגל!A1:E1', 'מיקומים בגוגל!C9:H9'],
        [conveSheet, 'המרות!A1:G1', 'המרות!B24:G24', 'המרות!O11:O17'],
        [lndpageSheet, 'דפי נחיתה!A1:D1'],
        [gaSheet, 'Google Ads!A1:F1']
    ];

I don't know the reason for it, because the sheet is already defined, but that what fixed it.

Upvotes: 1

Related Questions