Search code examples
pythongoogle-apigoogle-sheets-apigspread

How can I delete or update Google spreadsheet using Python import csv?

I'm working on my Python script and right now my script can import csv to spreadsheet, but I have to modify it to replace or delete the old spreadsheet that already imported and update it to the latest csv, since currently my script only working in upload and normal paste of data on csv file. What do I need to modify my script?

My script


def export_csv(o_csv, sheet_id):
    with open(o_csv, 'r') as csv_file:

       csvContents = csv_file.read()
    body = {
        'requests': [{
            'pasteData': {
                "coordinate": {
                    "sheetId": sheet_id,
                    "rowIndex": "0",
                    "columnIndex": "0",
                },
                "data": csvContents,
                "type": 'PASTE_NORMAL',
                "delimiter": ',',
            }
        }]
    }
Solution

  • I believe your goal is as follows.

    • After the sheet of sheet_id is cleared, you want to put the CSV data to the sheet of sheet_id.
    • You want to achieve this using gspread for python.

    In this case, how about the following modification?

    From:

    body = {
    'requests': [{
        'pasteData': {
            "coordinate": {
                "sheetId": sheet_id,
                "rowIndex": "0",
                "columnIndex": "0",
            },
            "data": csvContents,
            "type": 'PASTE_NORMAL',
            "delimiter": ',',
        }
    }]
}

    To:

    body = {
    "requests": [
        {"updateCells": {"range": {"sheetId": sheet_id}, "fields": "*"}}, # Added
        {
            "pasteData": {
                "coordinate": {
                    "sheetId": sheet_id,
                    "rowIndex": "0",
                    "columnIndex": "0",
                },
                "data": csvContents,
                "type": "PASTE_NORMAL",
                "delimiter": ",",
            }
        },
    ]
}
    • In this case, by adding one request to your request body, the sheet of sheet_id is cleared. And, after the sheet was cleared, the CSV data is put. By this, this request can be done by one API call.
    • In this case, fields use *. If you want to control the clear contents, please modify this.

    References: