Home > Mobile >  Google Apps Script, Google Sheets - Getting Spreadsheet ID and Sheet ID programmatically
Google Apps Script, Google Sheets - Getting Spreadsheet ID and Sheet ID programmatically

Time:06-24

I've been reading this without making any headway:

enter image description here

Take note that the first sheet always has an ID of 0 (gid=0) as the default value.

2.) Otherwise if what you are trying to do is get the Spreadsheet ID and Sheet ID from different spreadsheets it is not possible without copying the URL since there is no way it can identify which spreadsheet to refer to.

3.) Suggestion

What you can do if you need to get IDs of different files is to move them to a folder in your drive, then you can use the code below:

function listFilesInFolder(folderName) {
  
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn()).clearContent();
  
  sheet.appendRow(["Name", "Spreadsheet ID", "Sheet ID"]);

  //change the folder ID below to reflect your folder's ID (look in the URL when you're in your folder)
  var folder = DriveApp.getFolderById("HIS_SHOULD_BE_YOUR_FOLDER_ID");
  var contents = folder.getFiles();

  var cnt = 0;
  var file;

  while (contents.hasNext()) {
    var file = contents.next();
    cnt  ;

    data = [
      file.getName(),
      file.getId(),
    ];

    var spreadsheetSheets = SpreadsheetApp.openById(data[1]).getSheetByName("Sheet2").getSheetId();
    data.push(spreadsheetSheets);
    sheet.appendRow(data);
  };
};

Folder: enter image description here


Result: enter image description here

Note: In my example code I've made use of Sheet2 to see that it is working as expected. Please set accordingly to the name of the sheet which you are trying to get the ID.

Let me know if this works for you or if you have other questions.

References:

  • Related