How to loop through google sheets values? I can only do it for one set-CodePudding

SERVICE_ACCOUNT_FILE = 'service_account.json'
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']

creds = None

creds = service_account.Credentials.from_service_account_file(
        SERVICE_ACCOUNT_FILE, scopes=SCOPES)


spreadsheet_id = "" # 
range_a1Notation = "Mysheet!A6:A13" # issue is here


service = build('sheets', 'v4', credentials=creds)
sheet = service.spreadsheets()
result = sheet.values().get(spreadsheetId=spreadsheet_id, range=range_a1Notation).execute()
values = result.get("values", [])
df = pd.DataFrame(values)
df.to_csv('output.csv', index=False, header=False)


print (df)

This is my above code. I can enter A6:A1 and then get the full data outputted to my csv. The issue is I want to get more than one value printed out. I'm trying to fill the csv with 4 columns from my google sheets. How can I do that? I want to enter something like A6:13, F5:13, G1:13 and C:13.

CodePudding user response：

Try something like that:

SERVICE_ACCOUNT_FILE = 'service_account.json'
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']

creds = None

creds = service_account.Credentials.from_service_account_file(
    SERVICE_ACCOUNT_FILE, scopes=SCOPES)


spreadsheet_id = ""
range_a1Notation = ["Mysheet!A6:A13", "Mysheet!A6:A13"]  # issue is here

service = build('sheets', 'v4', credentials=creds)
sheet = service.spreadsheets()

dfObj = pd.DataFrame()

for i, v in enumerate(range_a1Notation):

    result = sheet.values().get(spreadsheetId=spreadsheet_id,
                                range=range_a1Notation[i]).execute()
    values = result.get("values", [])
    dfObj[i] = values

dfObj.to_csv('output.csv', index=False, header=False)


print(dfObj)

I did not check if the script works but try to catch the algorithm

CodePudding user response：

To read multiple data ranges from a Sheet, what about using spreadsheets.values.batchGet as below:

 range_a1Notation = [
            'Mysheet!A6:A13', 'Mysheet!B6:A13' ]

        result = service.spreadsheets().values().batchGet(
            spreadsheetId=SAMPLE_SPREADSHEET_ID, ranges=range_a1Notation).execute()
        ranges = result.get('valueRanges', [])

batchGet