Get list of sheets and latest sheet in google spreadsheet api v4 in Python

Question

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?

score 67 · Accepted Answer · edited Jul 12 '16 at 07:02

67

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)

edited Jul 12 '16 at 07:02

congusbongus

13,359
7
71
99

answered Jul 08 '16 at 16:23

user4426017

1,930
17
31

1

hi! Can you tell me about something equivalent to get the sheet metadata in javascript? – here_to_learn Aug 24 '16 at 18:09
1

source? Looking for node.js equivalent – allanberry Mar 27 '19 at 02:29

score 18 · Answer 2 · edited Nov 08 '17 at 19:48

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;

?>

score 16 · Answer 3 · answered Oct 20 '19 at 14:02

For those looking for a nodejs solution:

const { google } = require("googleapis")

// Assuming auth has already been generated
const getSheets = async (auth, spreadsheetId) => {
  const sheets = google.sheets({version: "v4", auth});
  const result = (await sheets.spreadsheets.get({ 
    spreadsheetId 
  })).data.sheets.map((sheet) => {
    return sheet.properties.title
  })
  return result
}

This is what gets returned from await sheets.spreadsheets.get({ spreadsheetId })

Then, using this type, I map over the result of data.sheets and return all of titles.

It looks like it returns the titles in order of how they are on the actual google sheet.

If you're trying to generate the auth token, then look here

score 10 · Answer 4 · edited Jul 11 '19 at 13:22

10

For python.

service = build('sheets', 'v4', credentials=creds)

sheet_metadata = service.spreadsheets().get(spreadsheetId=SHEET_ID).execute()

properties = sheet_metadata.get('sheets')
for  item in properties:

   if item.get("properties").get('title') == 'SHEET_TITILE':
       sheet_id = (item.get("properties").get('sheetId'))

print (sheet_id)

edited Jul 11 '19 at 13:22

cbuchart

10,847
9
53
93

answered Jul 11 '19 at 13:06

David Wainaina

101
1
3

accepted answer works with python, but this is also a good alternative. – Led Sep 08 '21 at 09:31

imvickykumar999 · Answer 5 · 2022-01-23T18:18:17.550

1

import gspread
from oauth2client.service_account import ServiceAccountCredentials as sac

scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']

creds = sac.from_json_keyfile_name('client_secrets.json', scope)
client = gspread.authorize(creds)
sheet = client.open_by_url(url) 

flag = True
i = 0

while flag:
    try:
        ith = sheet.get_worksheet(i)
        i += 1
        print(ith)
    except Exception as e:
        print(e)
        flag = False

print(f'Total worksheet = {i}')

# Output: 
# ---------------------------
# <Worksheet 'Form Responses 1' id:30127793>
# <Worksheet 'Sheet2' id:1033101728>
# index 2 not found
# Total worksheet = 2

edited Jan 23 '22 at 18:18

answered Jan 23 '22 at 18:02

imvickykumar999

19
1
6

Hi and thanks for the answer. It would be great if you could explain to us how and why your code solves the OP's problem as code itself is not always easy to read. – Simas Joneliunas Jan 27 '22 at 06:30
Yeah @Simas sure, I'll explain... I have called get_workshhet(i) function using sheet object in try part for each value of variable i starting from 0 in while loop, when get_worksheet function throw error for worksheet index 2 means for 3rd worksheet then code will go to except part and print "index 2 not found" in my case, means variable i had a value 0 and 1 means only 2 worksheet are in this sheet.... This was my logic part :) – imvickykumar999 Jan 28 '22 at 11:52

JohnH · Answer 6 · 2018-09-13T15:40:57.670

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

score 0 · Answer 7 · answered Dec 20 '21 at 12:46

  sheet_metadata = service.spreadsheets().get(spreadsheetId=sheet_id).execute()
    sheets = sheet_metadata.get('sheets', '')
    for title in sheets:
        print('%s' % (title.get("properties", {}).get("title", "Sheet1")))
        SAMPLE_RANGE_NAME = title.get("properties", {}).get("title", "Sheet1") ;
        result = sheet.values().get(spreadsheetId=sheet_id,
                                range=SAMPLE_RANGE_NAME).execute();
        print(result);
        values = result.get('values', []);
        if not values:
         print('No data found.')
        else:
         print('Name, Major:')
        for row in values:
            # Print columns A and E, which correspond to indices 0 and 4.
            print('%s, %s' % (row[0], row[1]))

Get list of sheets and latest sheet in google spreadsheet api v4 in Python

7 Answers7

Linked

Related