Retrieving Specific Columns:
To obtain specific column values and store them in a single array, you can utilize the reduce function. This array can then be incorporated into your spreadsheet row as follows:
function getDataFromSpreadsheet(ssID){
var ss = SpreadsheetApp.openById(ssID);
var ws = ss.getSheets()[0]
var data = ws.getRange("A1:I" + ws.getLastRow()).getValues();// how do I get the columns I want here (D & H)
data = data.reduce((acc,row) => acc.concat([row[3], row[7]]), []);
return data;
}
Subsequently, you will need to append this data to your primary sheet. Considering potential variations in the length of data from different spreadsheets, it is recommended to use appendRow within the loop rather than calling setValues just once outside. This approach accommodates differing lengths by appending rows individually:
function combinesheets() {
const folder = DriveApp.getFolderById(Copy_Folder_id)
const filesIterator = folder.getFiles();
const ws = SpreadsheetApp.openById(Stage_sheet_id);
const ss = ws.getSheetByName("Project List");
while(filesIterator.hasNext()){
file = filesIterator.next();
fileType = file.getMimeType();
if(fileType === "application/vnd.google-apps.spreadsheet"){
ssID = file.getId();
const data = getDataFromSpreadsheet(ssID);
ss.appendRow(data);
}
}
}
In cases where all columns are uniform in length, an alternative method entails:
function combinesheets() {
const folder = DriveApp.getFolderById(Copy_Folder_id)
const filesIterator = folder.getFiles();
const ws = SpreadsheetApp.openById(Stage_sheet_id);
const ss = ws.getSheetByName("Project List");
let combinedata = [];
while(filesIterator.hasNext()){
file = filesIterator.next();
fileType = file.getMimeType();
if(fileType === "application/vnd.google-apps.spreadsheet"){
ssID = file.getId();
const data = getDataFromSpreadsheet(ssID);
combinedata.push(data);
}
}
ss.getRange(2,1,combinedata.length, combinedata[0].length).setValues(combinedata);
}
Lastly, for cases where data from multiple sheets should be combined into a single row, consider using this more streamlined approach:
function combinesheets() {
const folder = DriveApp.getFolderById(Copy_Folder_id)
const filesIterator = folder.getFiles();
const ws = SpreadsheetApp.openById(Stage_sheet_id);
const ss = ws.getSheetByName("Project List");
let combinedata = [];
while(filesIterator.hasNext()){
file = filesIterator.next();
fileType = file.getMimeType();
if(fileType === "application/vnd.google-apps.spreadsheet"){
ssID = file.getId();
const data = getDataFromSpreadsheet(ssID);
combinedata.concat(data);
}
}
ss.getRange(2,1,1,combinedata.length).setValues([combinedata]);
}
File Transfer Process:
When relocating files to another folder, access the respective file and target folder via DriveApp, then execute the following command:
const file = DriveApp.getFileById("FILE_ID");
const folder = DriveApp.getFolderById("FOLDER_ID");
file.moveTo(folder);