Hi. I am trying to create a script that will look thru all my Spreadsheets in drive and search for a sheet with a specific name and return the sheets URL / ID and the Spreadsheet Name

This is what I got so far but its really slow and not efficient As I have about 190 Google Sheets In drive and Each sheet name I'm Looking for has a sheet in about 20 of the 190

I thought that if there is a way to do this by searching drive for the sheet name so it narrows down from all my google sheets to only the google sheets containing the sheet name value it might make it faster but I'm not sure how to do this and I don't think it will be fast enough for me

Thanks so much for trying to help me out with your professionalism

function getSheetNames(text = "Sheet Name To Search for") {
  var arraywithfoundsheets = [];
  var sp = SpreadsheetApp;
  // Get all Sheets in drive
  var allsheets = DriveApp.getFilesByType(MimeType.GOOGLE_SHEETS);
  //loop thru all spredsheets
  while (allsheets.hasNext()) {
    var spreadsheet = sp
      .open(allsheets.next());

    // return an array with all the sheet names from the spreadsheet
    var allsheetsinspreadsheet = spreadsheet.getSheets()
      .map(su => su.getName())

    // if this spredsheet contains a sheet with the specified name
    if (allsheetsinspreadsheet.indexOf(text) > -1) {

      // add the sheet with the specified name to the array
      arraywithfoundsheets.push([spreadsheet.getUrl() 
                                   '#gid=' 
                                   spreadsheet.
      getSheetByName(allsheetsinspreadsheet[allsheetsinspreadsheet.indexOf(text)])
                                    .getSheetId(),spreadsheet.getName()]);
    }
  }
Logger.log(arraywithfoundsheets);
return arraywithfoundsheets;
}

CodePudding user response：

Probably this will be a little bit faster (if it will work at all, I haven't tried):

function getSheetNames(text = "Sheet Name To Search for") {
  var arraywithfoundsheets = [];
  var sp = SpreadsheetApp;

  // Get all Sheets in drive
  var allsheets = DriveApp.getFilesByType(MimeType.GOOGLE_SHEETS);

  //loop thru all spredsheets
  while (allsheets.hasNext()) {
    var spreadsheet = sp.open(allsheets.next());
    var sheet = spreadsheet.getByName(text);   // no need to find for the sheet if you have its name
    if (!sheet) continue;                      // skip if there is no sheet with such name

    arraywithfoundsheets.push([
      spreadsheet.getUrl()   '#gid='   sheet.getSheetId(), // if you have the sheet already you can get its ID directly
      spreadsheet.getName()
    ]);

  }

  Logger.log(arraywithfoundsheets);
  return arraywithfoundsheets;
}

It makes less calls to the server. But actually I see no possibility to make the script noticeable faster.

CodePudding user response：

This does run but it takes a long time to open up each spreadsheet:

function getSheetUrlId(name = 'Sheet4') {
  const ss = SpreadsheetApp.getActive()
  const fit = DriveApp.getFilesByType(MimeType.GOOGLE_SHEETS);
  let shts = [];
  while (fit.hasNext()) {
    let ss = SpreadsheetApp.openById(fit.next().getId());
    if (ss) {
      let sh = ss.getSheetByName(name);
      if (sh) {
        let ssurl = ss.getUrl();
        let shid = sh.getSheetId();
        let url = `${ssurl}#gid=${shid}`
        shts.push({ url: url, id: sh.getSheetId(), name: sh.getName() });
      }
    }
  }
  Logger.log(JSON.stringify(shts));
}

I suspect a solution using the Sheets API may render a quicker solution. Bit I'm not that interested in finding out for myself.