CODEWITHSUNDEEP
pythongoogle-sheets-api
nexuscreator
nexuscreator

Reputation: 845

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

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?

Upvotes: 41

Views: 58052

Answers (7)

imvickykumar999
imvickykumar999

Reputation: 172

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

Upvotes: 1

kush
kush

Reputation: 645

  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]))

Upvotes: 0

Matt
Matt

Reputation: 321

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

Upvotes: 20

David Wainaina
David Wainaina

Reputation: 101

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)

Upvotes: 10

JohnH
JohnH

Reputation: 2133

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

Upvotes: 1

WOUNDEDStevenJones
WOUNDEDStevenJones

Reputation: 5315

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;

?>

Upvotes: 19

user4426017
user4426017

Reputation: 2000

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)

Upvotes: 68

Related Questions