Search code examples
google-apps-scriptgoogle-sheetsgoogle-workspace

Copying an Excel file from Google Drive to a newly created Folder using Script Editor

So this is the code I have for now.

function copyfile() {
var ss = SpreadsheetApp.getActive();
var activeSheet = ss.getSheetByName("sheet_Patients");
var activeSheetFM = ss.getSheetByName("_FolderMaker_");
var lastRow = activeSheet.getLastRow();
var data = activeSheet.getRange(lastRow,3,1,1).getValues();
var secondData = activeSheet.getRange(lastRow,4,1,1).getValues();
var folder_Name = [data+ ", " +secondData];

var id = DriveApp.getFoldersByName(folder_Name);
var finalId = activeSheetFM.getRange(2,4,1,1).setValue(id);

//var file = DriveApp.getFileById("1g25_24OTv_t5Qav2Q1hwEM_YBiPSaWWb");
//var source_folder = DriveApp.getFolderById("1pqh74miSSy9WDSD3kpi02kkI6XDuXiuU");
//var dest_folder = DriveApp.getFoldersByName(finalId);
//
//var file2 = file.makeCopy('BACKUP ' + Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd') + '.' + file.getName());
//dest_folder.addFile(file2);
//source_folder.removeFile(file2);
}

So i was aiming to get the folders name from the spreadsheet by using

var data = activeSheet.getRange(lastRow,3,1,1).getValues();
var secondData = activeSheet.getRange(lastRow,4,1,1).getValues();
var folder_Name = [data+ ", " +secondData];

and then combining it into an array to use on

var id = DriveApp.getFoldersByName(folder_Name);

and was hoping to get the folder ID this way by setting the values back to a separate sheet

var finalId = activeSheetFM.getRange(2,4,1,1).setValue(id);

but it returns as "FolderIterator". I'm not even sure if this method is viable as I am pretty new in coding.

Solution

  • Issues:

    • The first issue in your code is that you want to get the values of single cells:

      var data = activeSheet.getRange(lastRow,3,1,1).getValues();
var secondData = activeSheet.getRange(lastRow,4,1,1).getValues();

      but you are using getValues which returns a 2D array. Therefore, you need to replace getValues with getValue.

    • Last but not least, folders is a FolderIterator object as the error suggests. Namely, you need to iterate over it in the same way as in my solution. Keep in mind, that if you have multiple folders with the same name, this approach needs to be altered to get the folder of your choice. Because this approach will return multiple ids corresponding to each of the files with that name.

    Solution:

    function copyfile() {
var ss = SpreadsheetApp.getActive();
var activeSheet = ss.getSheetByName("sheet_Patients");
var activeSheetFM = ss.getSheetByName("_FolderMaker_");
var lastRow = activeSheet.getLastRow();
var data = activeSheet.getRange(lastRow,3,1,1).getValue();
var secondData = activeSheet.getRange(lastRow,4,1,1).getValue();
var folder_Name = data+ ", " +secondData;

var folders = DriveApp.getFoldersByName(folder_Name);
while (folders.hasNext()) {
      const folder = folders.next();
      var id = folder.getId(); 
} 
var finalId = activeSheetFM.getRange(2,4,1,1).setValue(id);
}

    Related articles: