1

As stated in the title, I'm trying to copy values from a range, clear the values that may exist in another range and finally paste the copied values into a range:

def copy_clear_paste(sheet_id, get_page_copy_name, range_copy, get_page_clear_name, range_clear, paste_page_name, range_paste):
    with open('my_user_agent.txt') as f:
        my_user_agent = f.read()

    headers = {
        'User-Agent': my_user_agent
        }

    CLIENT_SECRET_FILE = 'client_secrets.json'
    API_NAME = 'sheets'
    API_VERSION = 'v4'
    SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
    service = Create_Service(CLIENT_SECRET_FILE, API_NAME, API_VERSION, SCOPES)
    spreadsheet_id = sheet_id

    range_names = get_page_copy_name + '!' + range_copy
    result = service.spreadsheets().values().get(
                spreadsheetId=spreadsheet_id, range=range_names).execute()

    range_names_1 = get_page_clear_name + '!' + range_clear
    result_1 = service.spreadsheets().values().clear(
                spreadsheetId=spreadsheet_id, range=range_names_1).execute()

    worksheet_name = paste_page_name
    cell_range_insert = range_paste

    value_range_body = {
        'majorDimension': 'ROWS',
        'values': result
    }

    service.spreadsheets().values().append(
        spreadsheetId=spreadsheet_id,
        valueInputOption='USER_ENTERED',
        range=worksheet_name + '!' + cell_range_insert,
        body=value_range_body
    ).execute()

The error returned when I try to do this sequence of steps is:

googleapiclient.errors.HttpError: <HttpError 400 when requesting https://sheets.googleapis.com/v4/spreadsheets/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/Combinados%21A2:append?valueInputOption=USER_ENTERED&alt=json returned "Invalid JSON payload received. Unknown name "range" at 'data.values': Cannot find field.
Invalid JSON payload received. Unknown name "majorDimension" at 'data.values': Cannot find field.". Details: "[{'@type': 'type.googleapis.com/google.rpc.BadRequest', 'fieldViolations': [{'field': 'data.values', 'description': 'Invalid JSON payload received. Unknown name "range" at \'data.values\': Cannot find field.'}, {'field': 'data.values', 'description': 'Invalid JSON payload received. Unknown name "majorDimension" at \'data.values\': Cannot find field.'}]}]">

How should I proceed to be able to do this sequential work?

Tanaike
  • 181,128
  • 11
  • 97
  • 165
Digital Farmer
  • 1,705
  • 5
  • 17
  • 67

1 Answers1

1

In your script, how about the following modification?

From:

value_range_body = {
    'majorDimension': 'ROWS',
    'values': result
}

To:

value_range_body = {
    'majorDimension': 'ROWS',
    'values': result.get('values')
}
  • In your script, result is the returned value from service.spreadsheets().values().get(spreadsheetId=spreadsheet_id, range=range_names).execute(). This value cannot be directly used to the request body of service.spreadsheets().values().append(). I thought that this is the reason of your error message.

Note:

  • As another modification pattern, I thought that you can also use the following modification.

    • To

        del result['range']
  value_range_body = result

    • service.spreadsheets().values().get(spreadsheetId=spreadsheet_id, range=range_names).execute() returns an object of {'range': '###', 'majorDimension': 'ROWS', 'values': [[###]]}. So in your situation, by removing range property, I thought that the object can be used as the request body of service.spreadsheets().values().append().

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Hi mate, that's exactly my need. Thanks for support! Just one detail: from ```'values': 'values': result.get('values')``` to ```'values': result.get('values')```, I believe you accidentally duplicated it. Haha – Digital Farmer Apr 17 '22 at 01:31
  • 1
    @Brondby IF Thank you for replying. And, thank you for checking it. It's yes. So I modified it. And also, I added one more modified script. – Tanaike Apr 17 '22 at 01:33