0

I'm new to using Google Apps Script, I still need to read the documentation.

I have a list of sheets that are inside a folder, in this list (all Excel files), and these fields (GDrive_ID, File_Name, Full_Path, URL), I would like a function to read either the ID of the file or URL and count the number of sheets within each Google Workbook. I found this code on a forum and it served, only for the current worksheet, it would be possible to adapt it to search for this data inside the cell (which is actually the address for the worksheets), having with reference the ID, URL or name of the worksheet source?

I'm using this code in current file:

function getNumSheets() {
   return SpreadsheetApp.getActiveSpreadsheet().getSheets().length;
}
Rubén
  • 34,714
  • 9
  • 70
  • 166
Emerson Nery
  • 11
  • It sounds like you may have a list of URLs, you can open a spreadsheet by URL using [SpreadsheetApp.openByUrl()](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#openbyurlurl) – BeRT2me Jun 07 '22 at 22:23

1 Answers1

2

Count Sheets and Sheets

Convert All Files to Google Sheets: https://stackoverflow.com/a/56073634/7215091

function countSheetsAndSheets() {
  const folder = DriveApp.getFolderById("folderid");
  const files = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
  const shts = [];
  while (files.hasNext()) {
    let file = files.next();
    let ss = SpreadsheetApp.openById(file.getId());
    shts.push({ "name": file.getName(), "id": file.getId, "sheets": ss.getSheets().length })
  }
  //Logger.log(JSON.stringify(shts));
  return JSON.stringify(shts);
}
Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Actually I didn't phrase my question correctly with the initial post. I have 'google sheets' and 'excel files' inside this folder, for all of that I need a function to return the count of sheets inside it. In that file, that I've mentioned before, I listed URL, ID and name for each file, now I need a formula where I could put the ID or URL and this return this count. This formula didn't work. – Emerson Nery Jun 08 '22 at 12:15