1

I am reading data from google sheets, writing data to google sheets, and want to rearrange the sheets inside the worksheet:

CLIENT_SECRET_GOOGLE_SHEETS = r"client_secret_google_sheets.json"
creds = ServiceAccountCredentials.from_json_keyfile_name(CLIENT_SECRET_GOOGLE_SHEETS, GSHEETS_SCOPES)
client = gspread.authorize(creds)
sheet = client.open("my_worksheet")

worksheet_list = sheet.worksheets()

# worksheet_list 

[<Worksheet 'Documentation' id:35>,
 <Worksheet 'week 2' id:15>,
 <Worksheet 'week 1' id:20>]

I would like to change it to:

[<Worksheet 'Documentation' id:35>,
 <Worksheet 'week 1' id:20>,
 <Worksheet 'week 2' id:15>]

I tried searching for this functionality but was not able to find it in gspread documentation.

UPDATE

After using Tanaike's answer the order is not consistent:

[<Worksheet 'Documentation' id:35>,
 <Worksheet 'week 1' id:20>,
 <Worksheet 'week 10' id:150>,
 <Worksheet 'week 11' id:16>,
 <Worksheet 'week 2' id:115>,]
Jonas Palačionis
  • 4,591
  • 4
  • 22
  • 55

1 Answers1

1

I believe your goal is as follows.

  • You want to realign the sheets in a Google Spreadsheet.
  • You don't want to include the 1st worksheet. You want to sort other sheets except for 1st tab by the sheet name.
  • You want to achieve this using gspread for python.

In this case, how about the following sample script?

Sample script:

In this modification, the sheets except for the 1st tab are sorted by the batchUpdate method.

client = ### # Please use your client in your script.
spreadsheetId = "###" # Please set the Spreadsheet ID.

# 1. Retrieve all sheets.
spreadsheet = client.open_by_key(spreadsheetId)
sheets = spreadsheet.worksheets()
sheets.pop(0)

# 2. Sort sheets except for 1st tab.
sortedSheets = sorted(sheets, key=lambda x: x.title)

# 3. Create request body.
requests = []
for i, sheet in enumerate(sortedSheets):
    requests.append({
        "updateSheetProperties": {
            "properties": {
                "index": i + 1,
                "sheetId": sheet.id
            },
            "fields": "index"
        }
    })

# 4. Request to Sheets API.
res = spreadsheet.batch_update({"requests": requests})

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Thank you, but for some reason the sheet order is the following `week 1, week 10, week 11, week 12 /13/14/15/16/17, week 2, week 3`. For some reason only the first number is taken in to account, do you know why? – Jonas Palačionis Oct 21 '21 at 12:55
  • 1
    @Jonas Palačionis Thank you for replying. I apologize for the inconvenience. I had supposed your sample value. When your sheet names are `week 1, week 10, week 11, week 12 /13/14/15/16/17, week 2, week 3`, how about modifying from `sortedSheets = sorted(sheets, key=lambda x: x.title)` to `sortedSheets = sorted(sheets, key=lambda x: int(x.title.split(' ')[1]))`? If that was not the result you expect, I apologize again. – Tanaike Oct 21 '21 at 13:44
  • 1
    Thanks, I ended up sorting the list myself as there are more rules, but the general `batch_update` for sorting sheets work, thank you! – Jonas Palačionis Oct 22 '21 at 09:59
  • would you be able to have a look at my most recent question regarding google sheets API as you are a professional in that? – Jonas Palačionis Feb 28 '22 at 09:50
  • 1
    @Jonas Palačionis About `my most recent question`, if this question ( https://stackoverflow.com/q/71292680 ) is the question you want to resolve, it seems that your question has already been resolved. As another answer, in your script, you can modify `ws.update("HG3:HG5", [["test_1", "test_2", "test_3"]])` to `ws.update("HG3", [["test_1", "test_2", "test_3"]], major_dimension="COLUMNS")`. By this, those values are also put to the cells "HG3:HG5". – Tanaike Mar 01 '22 at 07:33