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.
Spreadsheet
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.
4
Merge the Data Into One Array
You can merge all the data in the other sheets by using getSheets(), map() and filter()
Script
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.
References:
- getSheets()
- getDataRange()
- map()
- filter()
- shift()
8