Copy values from a range, clear existing values in a range and finally paste the copied values-CodePudding

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!A2: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?

CodePudding user response：

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().

From:

To:

Note:

References: