CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Marek Kolo
Marek Kolo

Reputation: 37

Verifying value in the range from another range. If success, copy data to another sheet

I'd like to ask you for help.

I'm trying to make a spreadsheet where data will be put automatically, so I need a verification step, to check if the value matches the values in the range.

Here is an example printscreen of the spreadsheet

I need to check if the value in COLUMN A matches to the one of the value in range COLUMN H. If match, put OK to COLUMN F, if not put ERR.

Next step is copying data with success verification to another sheet

List of sheets

If the value is verified, an application should copy data from this row to the specific sheet.

For example: Value AAA will be verified correctly, so its row 2 and 6 (Period, Data1, Data2, Data3) should be copied to AAA sheet in particular place (Period).

Row 2 from Sheet1 -> Row 11 in AAA sheet

Row 6 form Sheet1 -> Row 12 in AAA sheet

AAA Sheet

Here is a link to the spreadsheet Spreadsheet link

I hope that this isn't too much to one question and everything is clear.

Thank you very much for your help.

Upvotes: 0

Views: 332

Answers (1)

Ritesh Nair
Ritesh Nair

Reputation: 3355

Try the below code...

function validate() {
    var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
    var data = ss.getRange(2, 1, ss.getLastRow() - 1,5).getValues();

    var valsToCheck = ss.getRange(2, 8, ss.getLastRow() - 1).getValues();
    valsToCheck = [].concat.apply([], valsToCheck).filter(String);

    var opt = [];var dataToCopied = [];

    for (var i = 0; i < data.length; i++) {
        if (valsToCheck.indexOf(data[i][0]) != -1) {
            opt.push(["OK"]);
            dataToCopied.push(data[i])
        } else {
            opt.push(["ERR"]);
        }
    }
    ss.getRange(2, 6, opt.length, 1).setValues(opt);
    var month = ["JANUARY","FEBRUARY","MARCH","APRIL","MAY","JUNE","JULY","AUGUST","SEPTEMBER","OCTOBER","NOVEMBER","DECEMBER"];

    for(var k=0;k<dataToCopied.length;k++){
      var rowNo = month.indexOf(dataToCopied[k][1].toString().toUpperCase());
      SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dataToCopied[k][0]).getRange(rowNo+3,2,1,3).setValues([[dataToCopied[k][2],dataToCopied[k][3],dataToCopied[k][4]]])
    }
}

Upvotes: 2

Related Questions