CODEWITHSUNDEEP
google-sheets
jsmanian
jsmanian

Reputation: 23

Script for condition based copy cells from one sheet to another sheet

I want to write a simple script to copy cells from "source" spreadsheet C5:K5 and paste it in "target" sheet in appropriate row based on value in B5. I could not able to find the exact scripting code. Next I attached sample spreadsheet which helps to describe what I am looking for:

https://docs.google.com/spreadsheets/d/1PoeIbmO1VTYYR9jViw6rHV0Pnzg357AkZJIA28My2LA/edit?usp=sharing

The following script is what I have tried, it is running fine, but it is not pasting the copied values in appropriate row. For example, if I run the script in shared spreadsheet "sample", values are pasted in row number - 101. But it should be pasted in row number - 55.

function create() {
    var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet1');
    var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet2');
    var dest;

    var range1 = sheet1.getRange(5,2);
    var tune = range1.getValue();
    Logger.log('tune is ' + tune);

    if (tune != null){ 
        var copyrange = sheet1.getRange("C5:J5");
    }

    var compare1 = sheet2.getRange("B5:B105");
    var compare = sheet2.getRange("B5:B105").getValues();
    Logger.log('sheet2 values are ' + compare); 
    var i;
    var dest=[];


    for(var j=1; j< compare.length; j++) {

        var find = compare[j];

        // Logger.log("find vlaue is" + find);
        //Logger.log("tune vlaue is" + tune);
        if (find = tune) {
            dest.push(compare[j]);
            //var r= compare.getRow.range();
        }
    }
    Logger.log('J value is' + j);

    copyrange.copyValuesToRange(sheet2, 3, 10, j, j);

}

Upvotes: 1

Views: 3584

Answers (1)

Werner
Werner

Reputation: 2557

As can be seen in the function documentation:

copyValuesToRange(sheet, column, columnEnd, row, rowEnd)

Copy the content of the range to the given location. If the destination is larger or smaller than the source range then the source will be repeated or truncated accordingly.

you need to specify the row and row end as last parameters. You were entering variable j which is the sheet1 "B5:B105" range when the for loop exhausts (the comparison with the if was doing nothing). Therefore, j takes value 105-5+1=101. If you want the copy to be placed at line 55, just change j to 55 in the line. But if you want it to be done automatically, you can use the following code:

function create() {
    var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet1');
    var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet2');
    var dest;

    var range1 = sheet1.getRange(5,2);
    var tune = range1.getValue();
    Logger.log('tune is ' + tune);

    if (tune != null){ 
        var copyrange = sheet1.getRange("C5:J5");
    }

    var compareRange = sheet2.getRange("B5:B105");
    var compareValues = compareRange.getValues();
    Logger.log('sheet2 values are ' + compareValues); 
    var i;
    var dest=[];


    for(var j=1; j< compareValues.length; j++) {

        // Logger.log("find vlaue is" + find);
        //Logger.log("tune vlaue is" + tune);
        if (compareValues[j] == tune) {
            break;
        }
    }
    Logger.log('J value is' + j);

    var startRow = compareRange.getRow();

    copyrange.copyValuesToRange(sheet2, 3, 10, j+startRow, j+startRow);

}

Upvotes: 3

Related Questions