CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Eric Mamet
Eric Mamet

Reputation: 3641

How can I open another google sheet within the same folder from within google app script

I am starting with google app script...

Within google app script in a google sheet, what might be the simplest way to open another google sheet within the same folder if I just know the name of this other google sheet

I am struggling to find a concept of "current folder" from within the Spreadsheet class.

Many things seem to work by FileId rather than name...

Upvotes: 1

Views: 788

Answers (2)

Tanaike
Tanaike

Reputation: 201378

I believe your goal is as follows.

  • You want to retrieve Google Spreadsheet in the same folder with the active Spreadsheet using the Spreadsheet title.
  • You want to achieve this using Google Apps Script.

In this case, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of your active Spreadsheet, set spreadsheetTitle you want to use, and save the script.

function myFunction() {
  const spreadsheetTitle = "###"; // Please set the Spreadsheet title you want to retrieve.

  const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const parentFolder = DriveApp.getFileById(activeSpreadsheet.getId()).getParents().next();
  const files = parentFolder.searchFiles(`title='${spreadsheetTitle}' and mimeType='${MimeType.GOOGLE_SHEETS}' and trashed=false`);
  if (!files.hasNext()) {
    console.log(`No spreadsheet with '${spreadsheetTitle}'is found.`);
    return;
  }
  const ss = SpreadsheetApp.open(files.next());

  console.log(ss.getId()); // As a sample. the spreadsheet ID of the retrieved Spreadsheet is shown in the log.

  const valuesOf1tab = ss.getSheets()[0].getDataRange().getValues();
  console.log(valuesOf1tab); // As a sample. the values of 1st tab from the retrieved Spreadsheet is shown in the log.
}

  • When this script is run, when the spreadsheet of spreadsheetTitle is found, you can see the spreadsheet ID in the log. When the spreadsheet of spreadsheetTitle is not found, you can see No spreadsheet with '###' is found. in the log.

  • In this sample script, it supposes that the spreadsheet of spreadsheetTitle is only one in the parent folder of the active spreadsheet. But, when there are multiple spreadsheets with the same filenames, the following sample script might be able to be used.

    function myFunction() {
  const spreadsheetTitle = "###"; // Please set the Spreadsheet title you want to retrieve.

  const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const parentFolder = DriveApp.getFileById(activeSpreadsheet.getId()).getParents().next();
  const files = parentFolder.searchFiles(`title='${spreadsheetTitle}' and mimeType='${MimeType.GOOGLE_SHEETS}' and trashed=false`);
  const spreadsheets = [];
  while (files.hasNext()) {
    spreadsheets.push(SpreadsheetApp.open(files.next()))
  }
  if (spreadsheets.length == 0) {
    console.log(`No spreadsheets with '${spreadsheetTitle}'is found.`);
    return;
  }
  console.log(`${spreadsheets.length} spreadsheets were found.`);
  spreadsheets.forEach(ss => {
    console.log(ss.getId()); // As a sample. the spreadsheet ID of the retrieved Spreadsheet is shown in the log.
    const valuesOf1tab = ss.getSheets()[0].getDataRange().getValues();
    console.log(valuesOf1tab); // As a sample. the values of 1st tab from the retrieved Spreadsheet is shown in the log.
  });
}

References:

Upvotes: 2

BJS
BJS

Reputation: 53

Try the following code. Note: must enable Drive API at Advanced Google Services before executing.

function retrieveFileObject(strFilename) {
  try {
    var fileList = DriveApp.getFilesByName(strFilename);
    if (fileList.hasNext()) {
      // found matching file - log it
      var objFile = fileList.next();
      var myString = "The file that was found is: " + objFile.getName();
      Logger.log(myString);
      return objFile;
    }
  }

  catch(err) {
    Logger.log('Failed with error %s', err.message);
  }
}


function exampleOpenGoogleSheet() {

  var objSheetFile = retrieveFileObject("myFileName");
  var ss = SpreadsheetApp.open(objSheetFile);
  var sheet = ss.getSheetByName("mySheetName");
  var data = sheet.getDataRange().getValues();
  Logger.log("This is the value in cell A1: ", data[0][0]);
}

Upvotes: 2

Related Questions