I've created a script from searches on stackoverflow and come up with this so far:
function collectData(){
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheets = ss.getSheets();
var target = ss.getSheetByName("Export");
for ( i = 0; i < sheets.length; i++){
var names = sheets[i].getRange("B1").getValue();
Logger.log(names);
}
}
This is working as expected but I am trying to then list the values in another sheet. I've tried copyTo and setValues and push to send the list of values to a specific sheet but keep getting errors.
To set values in some range of a spreadsheet, you need to arrange them in a double array. A row is [[x, y, z]]
, a column is [[x], [y], [z]]
. Since you want a column, the logic would be
var names = [];
for (var i = 0; i < sheets.length; i++){
names.push([sheets[i].getRange("B1").getValue()]);
}
target.getRange(1, 1, names.length, 1).setValues(names);
Reference: getRange.
To make a row instead, some square brackets get moved around:
var names = [];
for (var i = 0; i < sheets.length; i++){
names.push(sheets[i].getRange("B1").getValue());
}
target.getRange(1, 1, 1, names.length).setValues([names]);
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments