I am trying to get ranges from all tabs and put values in a specific tab called 'Data' at P
col.
I have range values at A2:B
between all tabs and am trying to get values by mapping method.
The issue is that each of the tabs values a firing increment in the array, which is why it won't be put in the data appropriately and only fires from the last tab.
I am tried:
function myFn() {
const ss = SpreadsheetApp.openById("id");
const forSheets = ss.getSheets();
const skip = ['Data'];
const ssData = ss.getSheetByName('Data');
forSheets.forEach(sheet => {
if (!skip.includes(sheet.getName())) {
const ssd = ssData.getRange("A2:B" + ssData.getLastRow()).getValues();
const tabs = sheet.getRange("A2:E").getValues();
const obj1 = new Map(tabs.map(([a, b, c, d, e]) => [`${a}${b}`, e]));
const res = ssd.map(([a, b]) => [obj1.get(`${a}${b}`) || null]);
ssData.getRange("P2:P").setValues(res);
console.log(res)
}
})
}
There are multiple tabs, 1 called "data" and the others, and now, just for test purposes, there are values in rows:
Other tab1
[ [ 31 ], [ 62 ], [ 88], [ null ], [ null ], [ null ],]
Other tab2
[ [ null ], [ null ], [ null ], [ 998 ], [ 262], [ 129 ],]
It seems I need something like this to fix the issue or need to merge all tab arrays: How to?
Other tab1
[ [ 31 ], [ 62 ], [ 88 ], [ null ], [ null ], [ null ],]
Other tab2
[ [ 221 ], [ 998 ], [ 262 ], [ null ], [ null ], [ null ],]
In the "Data" tab, I am just getting value *only from the last tab values.
You can merge all the data in the other sheets by using getSheets()
, map()
and filter()
You may refer to the sample script
below:
function collectData2() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var data = ss.getSheets().filter(sheet => sheet.getName() != "Data").map(x => x.getDataRange().getValues());
data.map(x => x.shift());
data1 = data.flat().map(x => x.filter((y, i) => (i == 0) + (i == 1) + (i == 4)));
var data0 = ss.getSheetByName("Data").getRange(2, 1, ss.getSheetByName("Data").getLastRow() - 1, 2).getValues();
var out = data0.map(x => data1.filter(y => (y[0] == x[0])*((y[1] == x[1]))).map(z => z[2])).map(a => a.length > 0 ? a : [null])
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data").getRange(2, 16, out.length, 1).setValues(out);
}
I manually set the column to be extracted The output of this script should look like the following:
wherein I tried random samples to check if the script did search for the values based on columns A and B.