Get list of sheets and latest sheet in google spre

问题:

I am trying to read and write values of different sheets in python 3 following the google official documentation. Though I am able to read values from certain sheets using range property in rangeName = 'Class Data!A2:E' in the code block mentioned below:

discoveryUrl = ('https://sheets.googleapis.com/$discovery/rest?'
                    'version=v4')
    service = discovery.build('sheets', 'v4', http=http,
                              discoveryServiceUrl=discoveryUrl)

    spreadsheetId = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms'
    rangeName = 'Class Data!A2:E'
    result = service.spreadsheets().values().get(
        spreadsheetId=spreadsheetId, range=rangeName).execute()
    values = result.get('values', [])

And I am trying to write values using the sample code from here:

requests.append({
    'updateCells': {
        'start': {'sheetId': 0, 'rowIndex': 0, 'columnIndex': 0},
        'rows': [
            {
                'values': [
                    {
                        'userEnteredValue': {'numberValue': 1},
                        'userEnteredFormat': {'backgroundColor': {'red': 1}}
                    }, {
                        'userEnteredValue': {'numberValue': 2},
                        'userEnteredFormat': {'backgroundColor': {'blue': 1}}
                    }, {
                        'userEnteredValue': {'numberValue': 3},
                        'userEnteredFormat': {'backgroundColor': {'green': 1}}
                    }
                ]
            }
        ],
        'fields': 'userEnteredValue,userEnteredFormat.backgroundColor'
    }
})
batchUpdateRequest = {'requests': requests}

service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id,
                                    body=batchUpdateRequest).execute()

The problem I am facing is that I am not able to retain latest sheet name or id from official documentation and as latest api revision is making random gid(we may not know what would be the sheet gid would be). Is there any way to refer list of sheets or spreadsheet latest revised sheet name or id using google sheet api v4?

回答1:

You can get a list of sheets by using the "get" method on spreadsheets:

sheet_metadata = service.spreadsheets().get(spreadsheetId=spreadsheet_id).execute()
sheets = sheet_metadata.get('sheets', '')
title = sheets[0].get("properties", {}).get("title", "Sheet1")
sheet_id = sheets[0].get("properties", {}).get("sheetId", 0)

回答2:

Nobody has asked about this on SO for the PHP library yet, but I just wanted to add this answer here as this is the first Google result for related questions.

<?php

$sheets = array();

// ... load library and set up client ...
$service = new Google_Service_Sheets($client);

$response = $service->spreadsheets->get($spreadsheetId);
foreach($response->getSheets() as $s) {
    $sheets[] = $s['properties']['title'];
}

return $sheets;

?>

回答3:

This is a C# method that returns the sheet id value of a specified sheet number and Spreadsheet id.

This was made with the Google Sheets API v4.

    private static Int32 GetSheetIdFromSheetNum(string sSpreadsheetId, Int32 iSheetNum, SheetsService gsService, ref string sTitle)
    {   //Get the Sheet Id for the specified sheet number and Spreadsheet id.

        Int32 iSheetId = -1;
        Int32 iLoop = -1;

        //Google.Apis.Sheets.v4.Data.Sheet gsSheet;
        Google.Apis.Sheets.v4.Data.Spreadsheet gsSpreadsheet;

        try
        {
            sTitle = string.Empty;
            gsSpreadsheet = gsService.Spreadsheets.Get(sSpreadsheetId).Execute();

            foreach (Sheet gsSheet in gsSpreadsheet.Sheets)
            {
                iLoop += 1;
                if (iLoop != iSheetNum)
                {
                    continue;
                }

                iSheetId = (gsSheet.Properties.SheetId).GetValueOrDefault(-1);      
                sTitle = gsSheet.Properties.Title;
                break;
            }

        }
        catch (Exception ex)
        {
            // Do error processing here.
        }

        return iSheetId;

    } //GetSheetIdFromSheetNum