CODEWITHSUNDEEP
google-apps-script
Delyana Boyadzhieva
Delyana Boyadzhieva

Reputation: 121

How to open a spreadsheet only by name of the corresponding file in Google Sheets?

I need a way to open a spreadsheet only by having the name of the corresponding file and the folder where the file is located. What I'm trying to do is creating a temporary copy of an active spreadsheet and reading information from the copy file rather than the active one. But unfortunately this copy file won't have a constant ID and URL as it's going to be erased every time after the script is done, and re-created with the next running of the script.

What I already tried is using the URL of the temporary file in order to open the spreadsheet itself, as you can see below.

But it's not useful for me, as I don't have a constant URL of this file (as it will be erased in the end of the script and have a new URL with the next script). But I have a constant name of the file, as well as this will be the only file in the respective folder.

What I also tried is looking for methods to open file only using its name (which is "temp_copy" in a 'temp_folder'). But I didn't find a working solution. 

var report = SpreadsheetApp.getActiveSpreadsheet();

var temp_folder = 
DriveApp.getFolderById("1EAsvVcFjIw5iGTeF_SxxQJLVlaLHl_pR");

DriveApp.getFileById(report.getId()).makeCopy("temp_copy", temp_folder);  

var temp_copy = SpreadsheetApp.openByUrl('  ');

var hiddenSheet = temp_copy.getSheetByName('DATE');

var lastRow = hiddenSheet.getSheetValues(1,1,1,1);

var sheet = temp_copy.getSheetByName('forCSV');

var lastColumn = sheet.getLastColumn();

var activeRange = sheet.getRange(1,2,lastRow,lastColumn);

What I'm expecting as a result is to make the link between the 3rd line of the code (when making a copy of the basic file) and 5th line of the code onwards (when referencing specific range from the temporary file) only by using name of the file or respective folder. In theory it seems so easy, I should be missing something somewhere...

Many thanks in advance!

Upvotes: 0

Views: 2033

Answers (1)

stewartmcgown
stewartmcgown

Reputation: 526

You can use DriveApp to get a FileIterator which contains the Sheet.

var file, files = DriveApp.getFilesByName("temp_copy");

if (files.hasNext ()){
   file = files.next(); // Get first result for name
} else {
   return "";
}

var sheet = SpreadsheetApp.openById(file.getId())

Upvotes: 2

Related Questions