google sheets api error: Details: "Invalid values[1][0]: list

Friends, I ask for help to make changes to google sheets. I need to write the following list in column B:

list = ['14.588,61\n224.715,38\n2.994,21\n308.782,90\n , 24.588,61\n324.715,38\n5.994,21\n508.782,90']

But it returns the following error.

<HttpError 400 when requesting https://sheets.googleapis.com/v4/spreadsheets/1UO606HO_oVnCb8-XENJcEk5Q3Pc6yy8z7ryEH_lfhfI/values/B2:B?valueInputOption=USER_ENTERED&alt=json returned "Invalid values[1][0]: list_value {
  values {
    string_value: "14.588,61\n224.715,38\n2.994,21\n308.782,90\n"
  }
}". Details: "Invalid values[1][0]: list_value {
  values {
    string_value: "14.588,61\n224.715,38\n2.994,21\n308.782,90\n"
  }
}
">

this is my code, can anyone help?

from __future__ import print_function
import os.path
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError


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

SAMPLE_SPREADSHEET_ID = '1UO606HO_oVnCb8-XENJcEk5Q3Pc6yy8z7ryEH_lfhfI'



def main():

    """
    LOGIN
    """
    creds = None
    if os.path.exists('token.json'):
        creds = Credentials.from_authorized_user_file('token.json', SCOPES)
    # Se não houver credenciais (válidas) disponíveis, deixe o usuário fazer login.
    if not creds or not creds.valid:
        if creds and creds.expired and creds.refresh_token:
            creds.refresh(Request())
        else:
            flow = InstalledAppFlow.from_client_secrets_file(
                'credentials.json', SCOPES)
            creds = flow.run_local_server(port=0)
        # Salve as credenciais para a próxima execução
        with open('token.json', 'w') as token:
            token.write(creds.to_json())


    try:
        service = build('sheets', 'v4', credentials=creds)
        sheet = service.spreadsheets()
        valores_adicionar = []
        q = [['14.588,61\n224.715,38\n2.994,21\n308.782,90\n'] , ['24.588,61\n324.715,38\n5.994,21\n508.782,90']]

        valores_adicionar.append(q)

        result = sheet.values().update(spreadsheetId=SAMPLE_SPREADSHEET_ID,
                                    range="B2:B", valueInputOption="USER_ENTERED",
                                    body={'values': valores_adicionar}).execute()

    except HttpError as err:
        print(err)


if __name__ == '__main__':
    main()

I don't understand why the error, when I change range="B2:B" by range="B2" the list is written in the worksheet, however, the first item in column B, the second in C. That is, each item in the list in one column. I need all of them to be in column B but on different lines.

CodePudding user response：

At "Method: spreadsheets.values.update", values is required to be 2 dimensional array. In your script, it is 3 dimensional array by valores_adicionar.append(q), because q is 2 dimensional array. I thought that this is the reason for your current issue of Invalid values[1][0].

About when I change range="B2:B" by range="B2" the list is written in the worksheet, in your current script, this cannot be done. So, I'm worried that you might have tested other scripts except for your showing script.

When this is reflected in your script, it becomes as follows.

From:

valores_adicionar = []
q = [['14.588,61\n224.715,38\n2.994,21\n308.782,90\n'] , ['24.588,61\n324.715,38\n5.994,21\n508.782,90']]

valores_adicionar.append(q)

result = sheet.values().update(spreadsheetId=SAMPLE_SPREADSHEET_ID,
                            range="B2:B", valueInputOption="USER_ENTERED",
                            body={'values': valores_adicionar}).execute()

To:

q = [['14.588,61\n224.715,38\n2.994,21\n308.782,90\n'] , ['24.588,61\n324.715,38\n5.994,21\n508.782,90']]

result = sheet.values().update(spreadsheetId=SAMPLE_SPREADSHEET_ID,
                            range="B2:B", valueInputOption="USER_ENTERED",
                            body={'values': q}).execute()

By this modification, the value of q is put into column "B". In this case, the result is the same even when range="B2:B" is changed to range="B2".

Note:

If you want to split the value of '14.588,61\n224.715,38\n2.994,21\n308.782,90\n' to '14.588,61', '224.715,38',,,, how about the following modification?

  q = [["14.588,61\n224.715,38\n2.994,21\n308.782,90\n"], ["24.588,61\n324.715,38\n5.994,21\n508.782,90"]]
  values = []
  for e in q:
      if f != "":
          values.append([f])
  result = sheet.values().update(spreadsheetId=SAMPLE_SPREADSHEET_ID, range="B2", valueInputOption="USER_ENTERED", body={"values": values}).execute()

If you want to separate the value with ,, please modify values.append([f]) to values.append(f.split(",")). In this case, the values are put to the columns "B" and "C".

If these were not your expected result, can you provide your expected result as an image? By this, I would like to modify the script.

Reference:

Method: spreadsheets.values.update