CODEWITHSUNDEEP
pythonpandasloopsgoogle-sheets
user12239916
user12239916

Reputation: 137

Writing a Json file, cell by cell into a google spreadsheet

I am in the process of automating a process, in which I need to upload some data to a Google spreadsheet. The data is originally located in a pandas dataframe, which is converted to a JSON file for upload.

I am getting to the upload, but i get all the data into each cell, so that cell A1 contains all data from the entire Pandas dataframe, in fact each cell in the spreadsheet contains all the data :/

How it looks right now

Of course, what I want to have happen is to place what is cell A1 in the dataframe, as A1 in the Google spreadsheet and so forth down to cell J173.

I am thinking I need to put in some sort of loop to make this happen, but I am not sure how JSON files work, so I am not succeeding in creating this loop.

I hope one of you can help

Below is the code

#Converting data to a json file for upload
csv_data = csv_data.to_json()


#Updating data
cell_list = sheet.range('A1:J173')

for cell in cell_list:
    cell.value = csv_data

sheet.update_cells(cell_list)

Windows 10 Python 3.8

Upvotes: 1

Views: 284

Answers (1)

Tanaike
Tanaike

Reputation: 201553

  • You want to put the data of dataframe to Google Spreadsheet.
    • In your script, csv_data of csv_data.to_json() is the dataframe.
  • You want to achieve this using gspread with python.
    • From your script, I understood like this.
  • You have already been able to get and put values for Google Spreadsheet using Sheets API.

Pattern 1:

In this pattern, the method of values_update of gspread is used.

Sample script:

spreadsheetId = "###"  # Please set the Spreadsheet ID.
sheetName = "Sheet1"  # Please set the sheet name.
csv_data =  # <--- please set the dataframe.

client = gspread.authorize(credentials)
values = [csv_data.columns.values.tolist()]
values.extend(csv_data.values.tolist())
spreadsheet.values_update(sheetName, params={'valueInputOption': 'USER_ENTERED'}, body={'values': values})

Pattern 2:

In this pattern, the library of gspread-dataframe is used.

Sample script:

from gspread_dataframe import set_with_dataframe # Please add this.

spreadsheetId = "###"  # Please set the Spreadsheet ID.
sheetName = "Sheet1"  # Please set the sheet name.
csv_data =  # <--- please set the dataframe.

client = gspread.authorize(credentials)
spreadsheet = client.open_by_key(spreadsheetId)
worksheet = spreadsheet.worksheet(sheetName)
set_with_dataframe(worksheet, csv_data)

References:

Upvotes: 1

Related Questions