How to use sheet ID in Google Sheets API?

Question

Google Sheets document can contain some sheets. First is default and '0'. Generally for any sheet there is address like this:

https://docs.google.com/spreadsheets/d/(spreadsheetId)/edit#gid=(sheetId)

with both spreadsheetId and sheetId.

But in API documentation there is no mention of how to use sheetId. I can only read and edit default sheet for given spreadsheetId.

If in request from code presented in exemplary link I added sheetId property I got error:

{ 
    message: 'Invalid JSON payload received. Unknown name "sheetId": Cannot bind query parameter. Field \'sheetId\' could not be found in request message.',
    domain: 'global',
    reason: 'badRequest' 
}

How to get access to other sheets than default in Google Sheets API and read or update fields in them?

@I'-'I, thank your hint I solved this problem. Please add this as answer, no comment and we close this topic. — Daniel, Oct 22 '18 at 18:17
amazing the docs still don't mention this in 2020, have a look at https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/update — cryanbhu, Nov 09 '20 at 11:07

TheMaster · Accepted Answer · 2022-02-06T15:42:53.637

6

Sheet name is the easiest way to access a specific sheet. As written here, range parameter can include sheet names like,

Sheet1!A1

If you must use a sheet id instead of sheet name, You can use any of the alternate end points which uses dataFilter, like spreadsheets.values.batchUpdateByDataFilter instead of spreadsheets.values.batchUpdate. You can then use sheetId in request body at data.dataFilter.gridRange.sheetId. An example of using such a filter with sheetId is provided by another answer here by ztrat4dkyle.

However, developer metadata is the preferred method of permanently associating objects(sheets/ranges/columns) to variables, where user modifications are expected on such objects.

edited Feb 06 '22 at 15:42

answered Oct 22 '18 at 18:41

TheMaster

45,448
6
62
85

6

downvoted because the question is about sheet id. By renaming a sheet, you can unknowingly break the name-based range. – Călin Darie May 28 '19 at 19:49
2

irrelevent answer. – Rahul Dec 19 '19 at 14:17
@Călin There's no relevant documentation stating that sheetId is constant. Furthermore, The title isn't the only question: As the question describes and elaborates: *How to get access to other sheets than default in Google Sheets API and read or update fields in them?* So even if sheetId is constant, what's wrong with code based on sheetName? Perhaps, I want to make the code to always refer to "Master" sheet. I might make a copy of another sheet and name it "Master" for the code to refer to that sheet. There's no mention of any need for immutability of the sheet in the question. – TheMaster Dec 19 '19 at 16:20
1

@Rahul How so? Even though I thought Calin's downvote is unjustified, I did edit my answer to show other possibilities. What's your justification for the downvote? – TheMaster Dec 19 '19 at 16:23
If any of you have additional requirements, ask a new question instead of downvoting my answer. The answer is perfect for the question. I can't improve the answer any more for a hypothetical problem by suggesting more unnecessary boilerplate(even though I did edit my suggestions into the answer because of the question "title"). – TheMaster Dec 19 '19 at 16:32
1

Sheet id may not constant but it can be find from url easily. Google docs give regex to get that also. this is not the accepted answer. – Arundev Mar 04 '20 at 12:45
3

Great answer! Instead of using ID. I like the idea of names instead of ID, works with ID as well. I voted it up – jeffci May 07 '20 at 19:24
hi, I know this is an old answer, but how do you use the sheet name inside a range parameter? let's say I am selecting row 5 in sheet "Example" using this: `'range': {'sheetId': sheetId,'startRowIndex': 5,'endRowIndex': 6} `, what should I replace the parameters in `range` with? – alph May 07 '23 at 08:46
@alph Which endpoint are you calling? – TheMaster May 07 '23 at 16:45
Thanks for responding even though it's an old question! I'm trying to call `spreadsheets.values.batchUpdate`. Right now I'm converting the sheetname into sheetId, then passing the sheetId into `range`. This works, but it takes one additional step. So I was wondering if there is a solution to use the A1 notation (e.g. `'Example!4:4'`) in batchUpdate directly. Hopefully my question is relevant to OP's and your answer – alph May 07 '23 at 18:37
1

@alph [`values.batchUpdate`](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchUpdate) accepts `data` as [`ValueRange`](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values#ValueRange) object array, which accepts `range` as ``string``(which supports ``Sheet!A1`` notation). So, I'm not sure where you're sending the `range` as a ``object``(or based on which documentation) – TheMaster May 07 '23 at 18:49
@alph `moveDimension` type of request doesn't support `range` as `string`. Only some requests do. Some don't. – TheMaster May 07 '23 at 19:01
Ok, so for this case I have no choice but to use json instead of a string A1 notation? BTW to anyone else that might be reading I deleted my previous comment, I was asking if requests such as `insertDimension` could use A1 notation – alph May 07 '23 at 19:03
1

@alph Yes. I believe so. – TheMaster May 07 '23 at 19:07

Oleksandr Hrin · Answer 2 · 2020-06-26T10:14:08.323

Here is my working example for "rename sheet in spreadsheet by sheetId" function. You can use other methods from Google Spreadsheets API Docs in the same way. Hope it will be helpful for somebody


    <?php
function getClient()   //standard auth function for google sheets API
{
    $clientConfigPath = __DIR__ . '/google_credentials/client_secret.json';
    $client = new Google_Client();
    $client->setApplicationName('Google Sheets API PHP Quickstart');
    $client->setScopes(Google_Service_Sheets::SPREADSHEETS);
    $client->setAuthConfig($clientConfigPath);
    $client->setAccessType('offline');

    // Load previously authorized credentials from a file.
    $credentialsPath = (__DIR__ . '/google_credentials/credentials.json');
    if (file_exists($credentialsPath)) {
        $accessToken = json_decode(file_get_contents($credentialsPath), true);
    } else {
        // Request authorization from the user.
        $authUrl = $client->createAuthUrl();
        printf("Open the following link in your browser:\n%s\n", $authUrl);
        print 'Enter verification code: ';
        $authCode = trim(fgets(STDIN));

        // Exchange authorization code for an access token.
        $accessToken = $client->fetchAccessTokenWithAuthCode($authCode);

        // Store the credentials to disk.
        if (!file_exists(dirname($credentialsPath))) {
            mkdir(dirname($credentialsPath), 0700, true);
        }
        file_put_contents($credentialsPath, json_encode($accessToken));
        printf("Credentials saved to %s\n", $credentialsPath);
    }
    $client->setAccessToken($accessToken);

    // Refresh the token if it's expired.
    if ($client->isAccessTokenExpired()) {
        $client->fetchAccessTokenWithRefreshToken($client->getRefreshToken());
        file_put_contents($credentialsPath, json_encode($client->getAccessToken()));
    }
    return $client;
}


function renameSheet(string $sheetId, string $newTitle, string $spreadsheetId)
{
    // Get the API client and construct the service object.
    $client = getClient();
    $service = new Google_Service_Sheets($client);

    $requests = [
        new Google_Service_Sheets_Request([
            'updateSheetProperties' => [
                'properties' => [
                    'sheetId' => $sheetId,
                    'title' => $newTitle,
                ],
                'fields' => 'title'
            ]
        ])
    ];

    $batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
        'requests' => $requests
    ]);

    return $service->spreadsheets->batchUpdate($spreadsheetId, $batchUpdateRequest);
}

UPDATE If you want to get sheet title by sheetId, you can use following function

function getSpreadsheetInfo($spreadsheetId)  
{
    $client = getClient();
    $service = new Google_Service_Sheets($client);

    $response = $service->spreadsheets->get($spreadsheetId);
    return $response;
}

function getSheets($spreadsheetId)  
{
    $spreadsheet_info = getSpreadsheetInfo($spreadsheetId);
    $sheets_info = [];
    foreach ($spreadsheet_info as $item) {
        $sheet_id = $item['properties']['sheetId'];
        $sheet_title = $item['properties']['title'];
        $sheets_info[$sheet_id] = $sheet_title;
    }
    return $sheets_info;
}

$sheets_info_array = getSheets($YOUR_SPREADSHEET_ID_HERE);

$sheets_info_array will be equal

array (
    "sheet_id1(int)" => 'sheet_title1',
    "sheet_id2(int)" => 'sheet_title3',
)

so you can get $your_sheet_id's title as $sheets_info_array[$your_sheet_id]

score 3 · Answer 3 · edited Mar 11 '21 at 03:04

Essentially we need to use dataFilters to target a specific sheet by ID.

@TheMaster pointed me in the right direction but I found the answers confusing so I just want to share my working example for Node.js.

Here's how to get the value of cell B2 from a sheet that has ID 0123456789

const getValueFromCellB2 = async () => {
  const SPREADSHEET_ID = 'INSERT_SPREADSHEET_ID';
  const SHEET_ID = 0123456789;
  // TODO: replace above values with real IDs.
  const google = await googleConnection();
  const sheetData = await google.spreadsheets.values
    .batchGetByDataFilter({
      spreadsheetId: SPREADSHEET_ID,
      resource: {
        dataFilters: [
          {
            gridRange: {
              sheetId: SHEET_ID,
              startRowIndex: 1,
              endRowIndex: 2,
              startColumnIndex: 1,
              endColumnIndex: 2,
            },
          },
        ],
      },
    })
    .then((res) => res.data.valueRanges[0].valueRange.values);

  return sheetData[0][0];
}

// There are many ways to auth with Google... Here's one:
const googleConnection = async () => {
  const auth = await google.auth.getClient({
    keyFilename: path.join(__dirname, '../../secrets.json'),
    scopes: 'https://www.googleapis.com/auth/spreadsheets',
  });

  return google.sheets({version: 'v4', auth});
}

To simply read data we're using batchGetByDataFilter where dataFilters is an array of separate filter objects. The gridRange filter (one of many) allows us to specify a sheetId and range of cells to return.

cloudxix · Answer 4 · 2020-06-25T05:56:45.320

The initial blank empty tab that is always present when a new Google Sheet is created always has sheetId 0 assigned to it.

Subsequently created sheetIds are randomized ten digit numbers. Only the first tab has sheetId 0. Even if you rename a sheet, it's ID remains constant. IDs are never reused - they remain unique within a given sheet.

Using the Google Drive API, access to a Google Sheet is instantiated using the sheet's Google Drive file ID.

Once you have instantiated access to the particular Google Sheet file, you can then reference each tab within the sheet tab and manipulate information, format, etc within a tab of the sheet, by using the 'sheetId' nomenclature.

Here is a PHP example of renaming a Google Sheet's tab name using sheetId 0.

<?php
/*
 *   Google Sheets API V4 / Drive API V3, rename existing sheet tab example
 *
 */
$fileID = '/* pass your Google Sheet Google Drive file ID here */';
$client = new Google_Client();
$client->useApplicationDefaultCredentials(); // the JSON service account key location as defined in $_SERVER
$client->setApplicationName('API Name');
$client->addScope(Google_Service_Drive::DRIVE);
$client->setAccessType('offline');
$client->setSubject('API Instance Subject');
$sheet = new Google_Service_Sheets($client);
$sheetList = $sheet->spreadsheets->get($fileID);

/*
 *   iterate through all Google Sheet tabs in this sheet
 */
 $homeFlag = FALSE;
 foreach($sheetList->getSheets() as $sheetRecord) {
        /*
         *   if match, save $sheetTabID from Google Sheet tab 
         */
         if ($sheetRecord['properties']['sheetId'] == 0) {
                 $sheetTabID = $sheetRecord['properties']['sheetId'];
                 $sheetTabTitle = $sheetRecord['properties']['title'];
                 $homeFlag = TRUE;
            }
    }

/*
 *   if $homeFlag is TRUE, you found your desired tab, so rename tab in Google Sheet
 */
 if ($homeFlag) {
         $newTabName = 'NotTabZero';
         $sheetRenameTab = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest(array('requests' => array('updateSheetProperties' => array('properties' => array('sheetId' => $sheetTabID, 'title' => $newTabName), 'fields' => 'title'))));
         $sheetResult = $sheet->spreadsheets->batchUpdate($sheetID,$sheetRenameTab);
    }
?>

score 1 · Answer 5 · answered Nov 28 '22 at 18:16

More simplier answer is to use the A1 Notation to get what sheet and rows you want

const res = await sheets.spreadsheets.values.get({
    spreadsheetId: "placeholder_id_value",
    range: "Sheet2!A:A", # This will grab all data out of sheet 2 from column A
  })

reference

How to use sheet ID in Google Sheets API?

5 Answers5

Linked

Related