How to create multiple sheets in a spreadsheet by gspread and python?-CodePudding

I am new to Python and wanna practice using gspread and python to deal with spreadsheets. Now I already knew how to connect google spreadsheet with gspread, but still cannot figure out how to create multiple sheets at once.

My expectation:

Create multiple sheets naming by Employees' names at once
So every employee can use their own sheet

Thanks in advance!

employee_name = ['Jonny','Emma', ...]

CodePudding user response：

From your following reply,

Actually, I think 2nd is just the consequence of the 1st request, so sorry for making misunderstanding! my thought is that I can use: worksheet = sh.add_worksheet(title="A worksheet", rows=100, cols=20) to create a worksheet, but i don't know how to create multiple worksheets.

I understood that you wanted to add multiple sheets in a Google Spreadsheet using gspread for python. In this case, how about the following sample script?

Sample script:

client =  # Please use your client.

employee_names = ["Jonny", "Emma",,,] # Please set the sheet names.
spreadsheetId = "###" # Please set your spreadsheet ID.

requests = [
    {
        "addSheet": {
            "properties": {
                "title": e,
                "gridProperties": {"rowCount": 100, "columnCount": 20},
            }
        }
    }
    for e in employee_names
]
spreadsheet = client.open_by_key(spreadsheetId)
spreadsheet.batch_update({"requests": requests})

In this sample script, the multiple sheets can be inserted by one API call by creating a request body. If sh.add_worksheet() is used, multiple API calls are required to be used. So, I proposed the above script.
Although I used "gridProperties": {"rowCount": 100, "columnCount": 20} from your showing script, in this case, even when "gridProperties": {"rowCount": 100, "columnCount": 20} is not used, the sheets are inserted as the default rows (1000) and columns (26).

References:

CodePudding user response：

You can do this, for example:

import gspread
from oauth2client.service_account import ServiceAccountCredentials

# Set up the credentials and client
scopes = ['https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', scopes)
client = gspread.authorize(credentials)

# Open the spreadsheet
spreadsheet = client.open("My Spreadsheet")

# Get the list of employee names
employee_names = ['Jonny', 'Emma', ...]

# Iterate over the list of names and create a sheet for each of the employee
for name in employee_names:
  spreadsheet.add_worksheet(title=name, rows=100, cols=20)

This is going to open the sheet, get the list of employees and loop over the list and in that way, you create a new sheet for each employee with the name of the actual employee as the sheet title. Hope it helps

References: https://docs.gspread.org/en/v5.7.0/