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

Question

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.

Answer 1

You have two main issues with this code:

1st issue - getValues

When you call getValues, if you have a Sheet like this:

Adn you call it on the whole range, it will return:

[
[1,2,3],
[4,5,6],
[7,8,9],
]

If you create a range object that only references one cell, like A1, you will get:

[[1]]

You can use getValue (without the s) to return the actual value. So if you use getValue on the range A1, it will return:

1

2nd Issue - FolderIterator

When you call this:

var id = DriveApp.getFoldersByName(folder_Name);

The variable id becomes a FolderIterator. So a better naming for this variable would be folders. Even if there is only one folder, it still returns an iterator, which you can think of like a list. To get the actual id of the folders within that list:

var folders = DriveApp.getFoldersByName(folder_Name);
while (folders.hasNext()) {
  var folder = folders.next();
  Logger.log(folder.getId());
}

References

• getValue Documentation
• getValues Documentation
• getFoldersByName documentation
• FolderIterator Class description

Answer 2

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:

• What does the range method getValues() return and setValues() accept?
• Searching for a Google-apps-script to iterate through a folder of sheets and append a range of data to a consolidated master sheet