CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
maxloo
maxloo

Reputation: 491

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

I've been reading this without making any headway:

https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app

Is there a way to programmatically get the Spreadsheet ID and Sheet ID of a particular Google SpreadSheet's sheet without manually typing or copying the URL?

Upvotes: 0

Views: 3825

Answers (2)

life12
life12

Reputation: 1

When the script is not bound to the spreadsheet you can still get IDs and use the IDs to make the move:

function moveSpreadsheet(spreadSheetID, newFolderID) {

  //Get the spreadsheet file
  let file = DriveApp.getFileById(spreadSheetID);

  //Get the target folder
  let newFolder = DriveApp.getFolderById(newFolderID);

  //Move the file to the new folder
  file.moveTo(newFolder);
}

To use this function you would need to already have the IDs. You can get the IDs by copying from the URLs as mentioned above. Spreadsheet IDs appear after "d/", and Folder IDs come at the end of the URL. If you already have the spreadsheet object, e.g. you programmatically created a new one, you can get its ID using the spreadsheet.getID() method.

Upvotes: 0

Logan
Logan

Reputation: 2140

1.) If the script is bound to the spreadsheet then you can just use getID and getSheetID as mentioned by TheMaster in the comments. See codes below:

function getIDs() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var spreadSheetID = ss.getId();
  var sheetID = sheet.getSheetId();
  console.log(spreadSheetID);
  console.log(sheetID);
}

Result: 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:

Upvotes: 1

Related Questions