How do we write data from a dictionary to a new row in google sheets?

Question

I wrote some code to gather the local business names, postcodes, addresses, and phone numbers. Now I want to write them to the next row in my google spreadsheet in the order I want them to. I'm new to this use of the gsheets API. Can anyone explain how we could do this?

I have a list of dictionaries where each dictionary will be written to the next row in the google sheet.

For example, lets say we have this list I've only used one dictionary for simplicity but in real code I'll have more than one dictionary:

businesses = [{name: 'Coffee_Shop', post_code: 'E6 7HJ', 'address1': '87 Longbridge Road', phone: '0773827354'}]

Update:

Im getting this error when using gspread method as seen below:

AttributeError: 'str' object has no attribute '__module__'

Here is the whole script:

import csv


import os.path

import gspread
from requests.api import post
import config

#User_Interact

zip_code = input('Please enter a ZIP Code. ')
term = input('What do you want to search for near {0}? '.format(zip_code))

#initalisations

business_to_be_made = []
bus = []
final = []
url = 'https://api.yelp.com/v3/businesses/search'

headers = {
    'Authorization':'Bearer ' + config.api_key
}
#The api_key must be generated by each user.

params = {
    'term': term,
    "location": zip_code
}

#contacts yelp api for data

response = requests.get(url, headers=headers, params=params)
total_response = response.json()
business_list = total_response['businesses']


#makes list of all businesses in the vicinity by type

for item in business_list:
    location = item['location']
    business_to_be_made.append({'name': item['name'],'zip_code': location['zip_code'], 'address': location['address1'], 'phone_number': item['phone'] })

sam_postcodes = open('file.txt', 'r')

postcodes_final = []

for item in sam_postcodes:
    postcodes_final.append(item)

global final_list
final_list = []
for business in business_to_be_made:
    if business['zip_code'] in postcodes_final:
        final_list.append(business)
    else:
        pass


credentials = 'API_KEY_HIDDEN'

client = gspread.authorize(credentials) # Please use this line for your script.

spreadsheet_id = str('SPREADHSEET_ID_HIDDEN') # Please set the Spreadsheet ID.
sheet_name = str('Sheet18') # Please set the sheet name.
order = ['name', 'post_code', 'address1', 'phone'] # This is the order you want to put the values.
  
values = [[o[e] for e in order] for o in final_list]
spreadsheet = client.open_by_key(spreadsheet_id)
sheet = spreadsheet.worksheet(sheet_name)
sheet.append_rows(values, value_input_option='USER_ENTERED')

Answer 1

I believe your goal is as follows.

• You want to append the values of businesses = [{name: 'Coffee_Shop', post_code: 'E6 7HJ', 'address1': '87 Longbridge Road', phone: '0773827354'}] in order you want to set.
• You want to achieve this using python.

In this answer, I would like to propose the following sample script? This sample script uses googleapis for python. So please do this flow of Quickstart for python. This script uses service = build('sheets', 'v4', credentials=creds) retrieved from this Quickstart.

Sample script:

service = build('sheets', 'v4', credentials=creds) # This is from the above Quickstart.

order = ['name', 'post_code', 'address1', 'phone'] # This is the order you want to put the values.
businesses = [{'name': 'Coffee_Shop', 'post_code': 'E6 7HJ', 'address1': '87 Longbridge Road', 'phone': '0773827354'}] # This is from your question.

spreadsheet_id = '###' # Please set the Spreadsheet ID.
sheet_name = 'Sheet1' # Please set the sheet name.

values = [[o[e] for e in order] for o in businesses]
res = service.spreadsheets().values().append(spreadsheetId=spreadsheet_id, range=sheet_name, valueInputOption='USER_ENTERED', body={'values': values}).execute()

• When this script is run, the values are appended to the sheet in order of ['name', 'post_code', 'address1', 'phone'].

Note:

• If you want to achieve this using gspread for python, you can also use the following script.

    client = gspread.authorize(credentials) # Please use this line for your script.
  
    spreadsheet_id = '###' # Please set the Spreadsheet ID.
    sheet_name = 'Sheet1' # Please set the sheet name.
    order = ['name', 'post_code', 'address1', 'phone'] # This is the order you want to put the values.
    businesses = [{'name': 'Coffee_Shop', 'post_code': 'E6 7HJ', 'address1': '87 Longbridge Road', 'phone': '0773827354'}] # This is from your question.
  
    values = [[o[e] for e in order] for o in businesses]
    spreadsheet = client.open_by_key(spreadsheetId)
    sheet = spreadsheet.worksheet(sheetName)
    sheet.append_rows(values, value_input_option='USER_ENTERED')
  

References:

• Python Quickstart for Sheets API
• Method: spreadsheets.values.append