Trying to import all google sheets files within a folder to a spreadsheet, all column headers are th-CodePudding

I need to be able to take all files from a a folder within drive and input the data into the spreadsheet. I am also creating a Menu Tab so I can just Run the script without going to editor. It would be great if I can create a way enter names of existing folder name without always going to the script in order to take out that extra step. This is the script I am using. I really need assistance with this.

function importTimesheets() {
 var spreadsheets = DriveApp.
   getFolderById("").
   getFilesByType(MimeType.GOOGLE_SHEETS);

 var data = [];
 while (spreadsheets.hasNext()) {
   var currentSpreadsheet = SpreadsheetApp.openById(spreadsheets.next().getId());
   data = data.concat(currentSpreadsheet   
                        .getSheetByName('Timesheet')
                      .getRange("A3:L10")
                        .getValues()
                          );
 }

 SpreadsheetApp.
   getActiveSheet().
   getRange(1, 1, data.length, data[0].length).
   setValues(data);
}


function onOpen() {
 var ui = SpreadsheetApp.getUi();
 ui.createMenu('Generate Timesheets')
     .addItem('Generate', 'importTimesheets')

CodePudding user response：

As far as I understand you are trying to find a solution for this line of code, whereby you currently have to enter the Id manually.

   DriveApp.getFolderById("")

My suggestions would be to prompt the user for the folder Id, because prompting for the folder name may cause errors if more than one folder has the same name. My suggestion is implemented as follows:

const folderId = SpreadsheetApp.getUi().prompt("Please enter the Folder Id").getResponseText()
const folder = DriveApp.getFolderById(folderId)

You could also use the Google File/Folder picker, as described here to search and select the folder.

CodePudding user response：

Try this:

var level=0;
function getFnF(folder = DriveApp.getRootFolder()) {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getSheetByName('Sheet1')
  const files=folder.getFilesByType(MimeType.GOOGLE_SHEETS);
  while(files.hasNext()) {
    let file=files.next();
    let firg=sh.getRange(sh.getLastRow()   1,level   1);
    firg.setValue(Utilities.formatString('File: %s', file.getName()));//need editing
  }
  const subfolders=folder.getFolders() 
  while(subfolders.hasNext()) {
    let subfolder=subfolders.next();
    let forg=sh.getRange(sh.getLastRow()   1,level   1);
    forg.setValue(Utilities.formatString('Fldr: %s', subfolder.getName()));//needs editing
    level  ;
    getFnF(subfolder);
  }
  level--;
}

function runThisFirst() {
  let r = SpreadsheetApp.getUi().prompt('Folder id','Enter Folder Id',SpreadsheetApp.getUi().ButtonSet.OK);
  let folder = DriveApp.getFolderById(r.getResponseText())
  getFnF(folder);
}