I am using Google Apps script, which is not bounded to a Google sheet. The target is the following:
- If the Google sheet
MV_DriveListingdoes not exist, create it. - If the Google sheet
MV_DriveListingexists, duplicate its active sheet.
I tried the following code, without success so far:
function createSS() {
var ss=DriveApp.getFilesByName('MV_DriveListing');
if (ss == null){
var newSS = SpreadsheetApp.create('MV_DriveListing');
SpreadsheetApp.setActiveSpreadsheet(newSS);
var mySS=SpreadsheetApp.getActiveSpreadsheet();
var sheet=SpreadsheetApp.getActiveSheet();
}
else {
var existingSS=SpreadsheetApp.open(ss);
SpreadsheetApp.setActiveSpreadsheet(existingSS);
var mySS=SpreadsheetApp.getActiveSpreadsheet();
mySS.duplicateActiveSheet();
}
}
It gives me the following error:
Exception: The parameters (DriveApp.FileIterator) don't match the method signature for SpreadsheetApp.open.
createSS @ Code.gs:23
Where is the error?
2
var ss=DriveApp.getFilesByName('MV_DriveListing'); if (ss == null){
The DriveApp.getFilesByName() method gets a FileIterator rather than an array. Use this instead:
if (ss.hasNext()) { //... do what you want with the file
mySS.duplicateActiveSheet();
There is no concept of “active sheet” when there is no user at the keyboard running the script. You may want to this pattern instead:
mySS.getSheets()[0].copyTo(mySS);
To get the first spreadsheet whose name is MV_DriveListing, and duplicate the first tab in that spreadsheet, or create a blank spreadsheet by that name when the file doesn’t exist, use something like this:
function createSS() {
const fileName = 'MV_DriveListing';
const iterator = DriveApp.getFilesByName(fileName);
let ss;
let newTab;
if (iterator.hasNext()) {
ss = SpreadsheetApp.open(iterator.next());
newTab = ss.getSheets()[0].copyTo(ss);
} else {
ss = SpreadsheetApp.create(fileName);
}
//... do what you want with ss and newTab; newTab will be undefined when ss didn't exist before
}
See DriveApp.getFilesByName() and Sheet.copyTo().
2