Warm tip: This article is reproduced from stackoverflow.com, please click
google-apps-script google-sheets

How to exchange values using temporary 2D array

发布于 2020-03-27 10:20:46

I am currently trying to exchange values between two columns (G and H)

The first sheet column G values don't move if they are found in a first column table reference in another sheet. if they are found in second column table reference, they have to swap values with the first sheet column H values the same index.

This code works for column G but not for column H. It gives the elements but not in order.

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet29'); //au cas ou ca bug
var lr = spreadsheet.getLastRow();
var lc = spreadsheet.getLastColumn();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet1 = ss.getSheetByName('Sheet29');
var sheet2 = ss.getSheetByName('Tabs of applications and firmwares');
var range1 = sheet1.getRange(2, 7, sheet1.getLastRow(), 9);
var range2 = sheet2.getRange(2, 1, sheet2.getLastRow(), 4);
var values1 = range1.getValues();
var values2 = range2.getValues();
var compteurBon = 0;
var compteurMauvais = 0;
var tempArray1 = [];


for (var i = 0; i < values1.length; i++) {
    for (var j = 0; j < values2.length; j++) {
        if (values1[i][0] === values2[j][0]) {
            compteurBon++;

        } else if (values1[i][0] == values2[j][2]) {
            compteurMauvais++;

            var tempArrayValue1 = values1[i][0];
            tempArray1.push(tempArrayValue1);
            values1[i][0] = values1[i][1];
            values1[i][1] = tempArray1; // here it doesn't take the right value

        }
    }
}
range1.setValues(values1);

};

Questioner
linoazone
Viewed
119
Jescanellas 2019-07-03 22:04

Okay, I think your code is in the good direction but it can be simplified. First of all, I understand that we have two sheets (“Sheet 29” and “Tabs of applications and firmwares”), and you want to copy the values from H to G in case G equals B from the second sheet. As I said in my comment, in case you want to just get two columns in the same range, you should use sheetx.getRange(2, col, sheet1.getLastRow(), 2); This last '2' is the number of columns you are getting into the range (G and H, A and B respectively). If we stick to your approach, we will need another tempArray variable:

for (var i = 0; i < values1.length - 1; i++) { 
   tempArray1.push(values1[i].toString().split(",")); 
   tempArray2.push(values2[i].toString().split(","));
}

What we are doing here is putting the 4 columns in those two arrays. Each one of them is 2D, so tempArray1 contains all the rows from G and H and the same for tempArray2 with A and B. You can access the data with [x][0] and [x][1] respectively. After this you can just compare the values of temparray1 and temparray2, and write the values from G (temparray[i][1]) to H (temparray[i, 7])

    if (tempArray1[i][0] == tempArray2[i][0]) { 

      compteurBon++; 

    } else if (tempArray1[i][0] == tempArray2[i][1]) {  

      sheet1.getRange(i+2 , 7).setValue(tempArray1[i][1]); 
    }