Google Sheets API - Create empty sheet - Slow performance

Question

Using the Python example taken directly from the "Google Sheets API documentation > Create a spreadsheet" and changing only the credentials, I am experiencing poor performance. It's taking ~5 seconds to create an empty spreadsheet.

This is my code with credentials redacted:

from googleapiclient.discovery import build
from googleapiclient.errors import HttpError
from google.oauth2.credentials import Credentials


def create(title):
    creds = Credentials(
        token="xxx",
        refresh_token="xxx",
        token_uri="https://oauth2.googleapis.com/token",
        client_id="xxx",
        client_secret="xxx",
    )

    try:
        service = build("sheets", "v4", credentials=creds)
        spreadsheet = {"properties": {"title": title}}
        import time

        start_time = time.time()
        spreadsheet = (
            service.spreadsheets()
            .create(body=spreadsheet, fields="spreadsheetId")
            .execute()
        )
        print(f"Time taken: {time.time() - start_time} seconds")
        print(f"Spreadsheet ID: {(spreadsheet.get('spreadsheetId'))}")
        return spreadsheet.get("spreadsheetId")
    except HttpError as error:
        print(f"An error occurred: {error}")
        return error


if __name__ == "__main__":
    # Pass: title
    import time

    start_time = time.time()
    create("mysheet1")
    print(f"Time taken: {time.time() - start_time} seconds")

Output:

Time taken: 4.405630826950073 seconds
Spreadsheet ID: xxx
Time taken: 4.407716989517212 seconds

I've looked elsewhere and I can only find solutions to optimise big spreadsheets but this is empty.

Why is this?
Is it expected?
Can I improve it?

Asking in the hope that Google reps will respond here.

Aside from credentials of oauth2 have you tried using service account? — Lime Husky
– Lime Husky, Commented Feb 5 at 19:02

Lime Husky · Accepted Answer · 2025-02-06 00:13:21Z

0

API Response Time

There are several factors why you are experiencing poor performance on creating a blank sheet and here's why.

Server processing time is the time the server takes to process the API request, execute the required operations, and return the response.
Network latency affects API response time, depending on the geographical distance between the user and the server, network congestion, and the internet connection quality.
Third-party dependencies APIs often rely on third-party services or data sources to provide information or perform specific functions.

You can also improve the performance by doing this optimizations such as.

Code optimization Reviewing and optimizing our API code can lead to more efficient processing and faster response times.
Compress responses to reduce transfer time Compressing API responses reduces the amount of data that needs to be received by the client.
Use asynchronous processing for long-running requests helps improve API response times by allowing the API to process multiple requests in parallel.

When I tried your code and run the response I get is this:

Time taken: 4.5286383628845215 seconds
Spreadsheet ID: xxxxxxxxxx
Time taken: 4.534226655960083 seconds

I refactored your code and added service account credential.

from googleapiclient.discovery import build
from googleapiclient.errors import HttpError
from google.oauth2 import service_account
import time

def create(title):
    SERVICE_ACCOUNT_FILE = r'path\credential.json'
    try:
        # Load the service account credentials
        creds = service_account.Credentials.from_service_account_file(SERVICE_ACCOUNT_FILE)
        service = build("sheets", "v4", credentials=creds)
        spreadsheet = {"properties": {"title": title}}

        start_time = time.time()
        spreadsheet = (
            service.spreadsheets()
            .create(body=spreadsheet, fields="spreadsheetId")
            .execute()
        )
        print(f"Time taken: {time.time() - start_time} seconds")
        print(f"Spreadsheet ID: {(spreadsheet.get('spreadsheetId'))}")
        return spreadsheet.get("spreadsheetId")
    except HttpError as error:
        print(f"An error occurred: {error}")
        return error

if __name__ == "__main__":
    start_time = time.time()
    create("mysheet1")
    print(f"Total time taken: {time.time() - start_time} seconds")

Output:

Time taken: 2.413567304611206 seconds
Spreadsheet ID: xxxxxxxxxx
Time taken: 2.418405771255493 seconds

Reference:

API Response Times: A Quick Guide to Improving Performance

edited Feb 6 at 0:13

answered Feb 5 at 19:32

Lime Husky

9231 gold badge4 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Stokedout Feb 7 at 6:52

Using a service account is not an option since the resulting Google Drive needs to be a real user who has authorized our app to create the sheet.

Lime Husky Feb 7 at 14:08

@Stokedout this is just an alternative and I found using service account is much faster than using credentials. Also I also explained why you are experiencing poor performance in your execution.

Stokedout Feb 7 at 16:44

The explanation is too general to be useful and since you experience the same response times I hope somebody from Google can share insight here.

Collectives™ on Stack Overflow

Google Sheets API - Create empty sheet - Slow performance

1 Answer 1

API Response Time

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

API Response Time

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related