How to get spreadsheet sheet id through API?

0
votes

I am trying to delete excess rows and columns (known range) from a list of google sheets (around 570 sheets) in a specific directory.

I was able to get the spreadsheet Id's using the google drive API. However, modifying the dimensions of a page require the sheet Id.

I was unable to find a way of retrieving this the sheet Id through the drive API (file properties) nor was I able to find any documentation on how I can list the available sheets within a spreadsheet given that I have the spreadsheet Id.

I tried using google apps script to reach my goal, but the run time has exceeded the allowed limit (5 minutes) even after exhausting all possibility of optimizing the code.

1
google-apigoogle-drive-apigoogle-sheets-api

1 Answers

1
votes

You can use spreadsheets.get() to get all the sheets id available in your spreadsheet. You just need to provide the spreadsheet id in your path parameters, you can leave the query parameters empty.

It will return a Spreadsheet Resource which contains sheets information under sheets -> properties field. See Sheets Object Resource

Sample Spreadsheet:

enter image description here

Sample Request using API explorer:

enter image description here

Sample Response:

{
  "spreadsheetId": "1RPQ7WOTOZZSirf60kP6nULhViCdsuGHcOVoUxxxx",
  "properties": {
    "title": "NewSheet",

    ......
    
    },
    
  ......
  
  "sheets": [
    {
      "properties": {
        "sheetId": 0,
        "title": "Sheet1",
        "index": 0,
        "sheetType": "GRID",
        "gridProperties": {
          "rowCount": 1000,
          "columnCount": 26
        }
      }
    },
    {
      "properties": {
        "sheetId": 500406870,
        "title": "Sheet2",
        "index": 1,
        "sheetType": "GRID",
        "gridProperties": {
          "rowCount": 1000,
          "columnCount": 26
        }
      }
    }
  ],

}