How do I rename a (work)sheet in a Google Sheets spreadsheet using the API in Python?

Question

I have been trying/looking to solve this problem for a long while. I have read the documentation for gspread and I cannot find that there is a way to rename a worksheet. Any of you know how to? I would massively appreciate it! There is indeed worksheet.title which gives the name of the worksheet, but I cannot find a way to rename the actual sheet.

Thank you in advance!

You can achieve this with the help of Google Apps Script. You can use the rename(newName) to rename the docu. var ss = SpreadsheetApp.getActiveSpreadsheet(); ss.rename("This is the new name"); And if you want to rename the current active sheet to the given new name, you can call renameActiveSheet(newName). // The code below will rename the active sheet to "Hello world" SpreadsheetApp.getActiveSpreadsheet().renameActiveSheet("Hello world"); For more info. check this [documentation](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#renamenewname) — KENdi, Jun 29 '16 at 23:58
It seems to be related with the following request: "updateSheetProperties": { object(UpdateSheetPropertiesRequest) }, with UpdateSheetPropertiesRequest being described as: "Updates properties of the sheet with the specified sheetId ." ... except I found no way to set the sheetId https://developers.google.com/sheets/reference/rest/v4/spreadsheets/request#updatesheetpropertiesrequest — Christophe, Oct 01 '16 at 14:14
@Christophe I tihk that the hardest part to comprehend in the Google Sheet API Doc, regardin the `UpdateSheetPropertiesRequest` is the `fields` param which tell the API what the request should edit during the request. In my example below the `sheetId` is used to update the title only. I don't know if, changing the `fields` param to "sheetId", that request would be able to update the sheetId (for example) — Mattia Galati, Oct 04 '16 at 06:27
Can you just copy the worksheet data to another one with a different name and delete the original? How large is the file you are working with? — ytpillai, Oct 06 '16 at 17:56
`worksheet.update_title(title)` used to work. but doesn't work anymore. — manas, Nov 02 '20 at 05:48

score 28 · Answer 1 · answered Oct 03 '16 at 16:56

28

This is an extraction of a library which I've coded personally:

def _batch(self, requests):
    body = {
        'requests': requests
    }
    return self._service.spreadsheets().batchUpdate(spreadsheetId=self.spreadsheetId, body=body).execute()

def renameSheet(self, sheetId, newName):
    return self._batch({
        "updateSheetProperties": {
            "properties": {
                "sheetId": sheetId,
                "title": newName,
            },
            "fields": "title",
        }
    })

I think that with a little effort, you can implement it into your code and obtain what you want. In order to make the batchUpdate call, you will need the spreadsheetId as well as the initialized service as explained in the Python QUickstart - Google Sheet API

answered Oct 03 '16 at 16:56

Mattia Galati

1,305
13
18

1

This answer has just super simplified "FieldMasks". Why on earth it has to be so complicated but great answer. – Antony Jun 02 '17 at 14:04
1

I agree with you, the only answer I could think is that Google has adopted this approach in order to abstract from any kind of feature and support almost any kind of operation with a single method – Mattia Galati Jun 12 '17 at 07:30
Tip: in case you want to rename the first sheet and you don't have the sheetId, you may omit the sheetId property – Asaf Shveki Sep 01 '19 at 14:45
if you want `batchupdate UpdateSheetProperties` api call to _modify several properties at once_, look at [JSON request structure](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#UpdateSheetPropertiesRequest): add your "properties", and **_comma-separate_** their names inside "fields" string content. [FieldMasks](https://developers.google.com/protocol-buffers/docs/reference/google.protobuf#google.protobuf.FieldMask) format is complicated but I found a good explanation [here](https://github.com/googleapis/google-api-nodejs-client/issues/1755#issuecomment-61310491) – abu Apr 18 '21 at 11:28

score 4 · Answer 2 · edited Jun 20 '20 at 09:12

Your answer can be solved via a HTTP request from Python.

Link is here

You need to send some sort of metadata for the worksheet via HTTP.

For example, get the ID of the worksheet using Python, and send the following info:

<entry>
  <id>
    https://spreadsheets.google.com/feeds/worksheets/key/private/full/worksheetId
  </id>
  <updated>2007-07-30T18:51:30.666Z</updated>
  <category scheme="http://schemas.google.com/spreadsheets/2006"
    term="http://schemas.google.com/spreadsheets/2006#worksheet"/>
  <title type="text">Income</title>
  <content type="text">Expenses</content>
  <link rel="http://schemas.google.com/spreadsheets/2006#listfeed"
    type="application/atom+xml" href="https://spreadsheets.google.com/feeds/list/key/worksheetId/private/full"/>
  <link rel="http://schemas.google.com/spreadsheets/2006#cellsfeed"
    type="application/atom+xml" href="https://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full"/>
  <link rel="self" type="application/atom+xml"
    href="https://spreadsheets.google.com/feeds/worksheets/key/private/full/worksheetId"/>
  <link rel="edit" type="application/atom+xml"
    href="https://spreadsheets.google.com/feeds/worksheets/key/private/full/worksheetId/version"/>
  <gs:rowCount>45</gs:rowCount>
  <gs:colCount>15</gs:colCount>
</entry>

The website has a Java and .NET solution as well. (This is for the legacy version 3)

For the newer version, you can use a batch update via a POST http request from Python as well.

link is here

The data for the request is

{
  "requests": [{
      "updateSpreadsheetProperties": {
          "properties": {"title": "My New Title"},
          "fields": "title"
        }
    }]
}

to be sent via POST to https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate

In both requests, replace the spreadsheetId in the URL with the ID of the Google Sheet you are editing.

Notice the change from v3 to v4 in the URLs.

If you are using a version 3 application and want to migrate, the link to that is here

EDIT

A commentor noted that the second request does not change name of a worksheet. The link I added shows the way to change intricate properties of a worksheet, I will be updating my answer soon.

Notice that the request you have posted is related to change the **Spreadsheet** (so your request would rename the entire Spreadsheet), but the user clearly requested how to rename the **Worksheet**. — Mattia Galati, Oct 07 '16 at 06:45
Ah yes, I realise that the second request does not do that, however, the page I have linked to shows the more intricate properties of the spreadsheet that can also be edited. However, the deprecated (but still functional) API request should change the name of a worksheet as well, given that the OP can get the ID of the worksheet from their code (they mentioned they can get title, so they can ID as well) — ytpillai, Oct 08 '16 at 15:53
All that needs to be added in order to update a specific sheet is to specify a sheetID, e.g. `{ "requests": [{ "updateSheetProperties": { "properties": { "sheetId": "My Sheet ID", "title": "My New Title" }, "fields": "title" } }] }`; the http address is the same. — Luke, Feb 13 '21 at 08:31

score 3 · Answer 3 · edited Feb 14 '20 at 18:50

3

You could achieve the same with the gspread port for api v4: pygsheets (author here).

The corresponding code using pygsheets would be:

import pygsheets

gc = pygsheets.authorize()

# open spreadsheet and then worksheet
sh = gc.open('my new spreadsheet')
wks = sh.sheet1
wks.title = 'new title'

edited Feb 14 '20 at 18:50

jorijnsmit

3,893
4
24
48

answered Feb 20 '17 at 20:14

Nithin

3,969
25
37

score 1 · Answer 4 · answered Sep 16 '19 at 12:06

If you are using Node, here's what worked for me:

import {google} from 'googleapis';

const auth = new google.auth.OAuth2(...)

const sheetsService = google.sheets({version: 'v4', auth})

const requests = [
 {
  updateSheetProperties: {
   properties: {
    sheetId: 'id-of-the-sheet-that-you-want-to-rename',
    title: 'new-title',
   },
   fields: 'title'
   }
  }
];

sheetsService.spreadsheets.batchUpdate({
 spreadsheetId: 'some-spreasheet-id',
 requestBody: {
  requests,
 },
});

score 1 · Answer 5 · answered Nov 03 '19 at 08:10

This can also be implemented with Google API methods only.

Here is a json-free solution.

sheetsService = getSheetsService();
// create a SheetProperty object and put there all your parameters (new title, sheet id, something else)
SheetProperties title = new SheetProperties().setSheetId(0).setTitle("Main");
// make a request with this properties
UpdateSheetPropertiesRequest rename = new UpdateSheetPropertiesRequest().setProperties(title);
// set fields you want to update
rename.setFields("title");
// as requestBody.setRequests gets a list, you need to compose an list from your request
List<Request> requests = new ArrayList<>();
// convert to Request
Request request = new Request().setUpdateSheetProperties(rename);
requests.add(request);
BatchUpdateSpreadsheetRequest requestBody = new BatchUpdateSpreadsheetRequest();
requestBody.setRequests(requests);
// now you can execute batchUpdate with your sheetsService and SHEET_ID
sheetsService.spreadsheets().batchUpdate(SHEET_ID, requestBody).execute();

Here you can find more info on sheetProperties

Indeed this is perfect solution with Java. Worked like a charm. Thank you! — Gaurav Khandelwal, May 02 '20 at 17:28

score 0 · Answer 6 · answered May 17 '20 at 09:07

0

For those whose solving this renaming using NodeJS. Just use the batchRequest API. Indicate in the sheetID the sheet id youre editing and the title field the new title. Then indicate "title" in the fields.

answered May 17 '20 at 09:07

Drix Lopez

31
3

score 0 · Answer 7 · answered Jan 15 '21 at 16:45

In PHP

    public function updateSheetTitle(string $fileId, string $title, ?int $sheetId = NULL): void
    {
        if (!$sheetId) {
            // rename first sheet
            $file = $this->get($fileId);
            if (!$file->getSheets()) {
                throw new NoSheetsException();
            }
            $sheetId = $file->getSheets()[0]->getProperties()->getSheetId();
        }
        $titleProp = new Google_Service_Sheets_SheetProperties();
        $titleProp->setSheetId($sheetId);
        $titleProp->setTitle($title);

        $renameReq = new Google_Service_Sheets_UpdateSheetPropertiesRequest();
        $renameReq->setProperties($titleProp);
        $renameReq->setFields('title');

        $request = new Google_Service_Sheets_Request();
        $request->setUpdateSheetProperties($renameReq);

        $batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest();
        $batchUpdateRequest->setRequests([$request]);

        $this->spreadsheets->batchUpdate($fileId, $batchUpdateRequest);
    }

abu · Answer 8 · 2021-04-18T10:38:13.153

Although the title says using the API, the question details OP wanted to use gspread API (instead of using direct calls to Google Spreadsheets API).

I am surprised that there is no answer to this yet. Perhaps gspread had not a method for this when the question was posted, but now it is as simple as using update_title method:

import gspread

# authenticate:
gc = gspread.service_account()
# choose a gspread method to open your spreadsheet and your worksheet:
wsh = gc.open_by_key(spreadsheetId).worksheet("old_WorkSheetName")
# update your worksheet name:
wsh.update_title("new_WorksheetName")

That's it.

How do I rename a (work)sheet in a Google Sheets spreadsheet using the API in Python?

8 Answers8

EDIT

Linked