I've been working on this script for two weeks now and can't figure it out. I think I need to combine these two functions to create one array and use the getSheetNames() function to be more of a descriptor. Then I want to run a separate function to filter the array into two different sheets on a master document based on sheet name. Any help would be greatly appreciated.
//Function to pull all data from workbook
function getDataFromSpreadsheet1(ssID) {
//Open workbook
let ss = SpreadsheetApp.openById(ssID);
//Get all sheets in workbook and put into array
let data = ss.getSheets().map(sh => sh.getRange(4, 1, sh.getLastRow() - 2, 3).getValues()).flat(1);
Logger.log(data);
return data;
}
//Function to return name of sheets in workbook
function getSheetNames(ssID) {
let ss = SpreadsheetApp.openById(ssID);
let sheets = ss.getSheets();
let session = [];
sheets.forEach(function (sheet) {
session.push(sheet.getName());
});
Logger.log(session);
return session;
}
CodePudding user response:
Combining multiple Sheets of multiple spreadsheets
function getDataFromSpreadsheet1(ssID) {
const ssids = ['ssID1', 'ssID2'];
data = []
ssids.forEach(ssid => {
let ss = SpreadsheetApp.openById(ssid);
let vs = ss.getSheets().map(sh => sh.getRange(4, 1, sh.getLastRow() - 2, 3).getValues()).flat(1);
data.push(vs)
})
Logger.log(data);
return data;
}
CodePudding user response:
Here's what you need.
function getDataFromSpreadsheet1(ssID) {
//Open workbook
let ss = SpreadsheetApp.openById(ssID);
//Get all sheets in workbook and put into array
let data = ss.getSheets().map(sh =>{
return [[sh.getName()],sh.getRange(4, 1, 3, 3).getValues().flat(1)]
});
console.log(data);
return data;
}