title: How to format pandas dataframe into google sheets api properly
body: <p>I am trying to get some data from python to google sheets through the google sheets API. The data is formatted through a df where amounts_a, shoesize_a and frequency_a are all lists</p>
  <pre><code>  df_asks = pd.DataFrame(
              {
                  &quot;Asks(USD)&quot;:amounts_a,
                  &quot;Shoes Size(US)&quot;:shoesize_a,
                  &quot;Frequency&quot;:frequency_a 
              }
          )
  </code></pre>
  <p><a href="https://i.stack.imgur.com/kbk0M.png" rel="nofollow noreferrer">Present dataframe format, printed</a></p>
  <p>Code used to update the spreadsheet:</p>
  <pre><code>service = discovery.build('sheets', 'v4', credentials=creds)
  
  spreadsheetId = &quot;xxx&quot;
  range_ = sheetname + coordinate 
  value_input_option = &quot;USER_ENTERED&quot;
  value_range_body = {
      &quot;majorDimension&quot;:&quot;COLUMNS&quot;,
      &quot;values&quot;:
          [data]
      }
  request = service.spreadsheets().values().update(spreadsheetId=spreadsheetId, range=range_, 
                                                  valueInputOption=value_input_option, body=value_range_body)
  print (request)
  response = request.execute()
  </code></pre>
  <p>When trying to update the spreadsheet values with the present data format, I receive the following error
  &quot;TypeError: Object of type DataFrame is not JSON serializable&quot;</p>
  <p>I converted the dataframes to json using (.to_json()), which then works but the data format in the spreadsheet is the <a href="https://i.stack.imgur.com/afKQq.png" rel="nofollow noreferrer">entire dataframe in one line</a></p>
  <p>How can I transfer this dataframe into sheets with the proper format (as seen in the printed version)?</p>
  <p>I am new to working with dataframes and the api, any help would be appreciated</p>