CODEWITHSUNDEEP
google-oauthgoogle-sheets-apigoogle-api-python-client
Stokedout
Stokedout

Reputation: 11055

Google Sheets API - Create empty sheet - Slow performance

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.

Asking in the hope that Google reps will respond here.

Upvotes: 1

Views: 86

Answers (1)

Lime Husky
Lime Husky

Reputation: 890

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

Upvotes: 0

Related Questions