Copy spreadsheet sheet to a another one with Google API using Python

Question

I am trying to create a google spreadsheet from a template and then edit its values (cells). Manually, I just visit the original spreadsheet and click Make a copy from the File menu. I have not found a way to do that using Python3 and gspread. So, I am trying to find a workaround instead.

So, I am using a Python script in order to create new google sheets as shown in the snippet (I only used it as a viewer for this question here):

import gspread
from oauth2client.service_account import ServiceAccountCredentials

from pprint import pprint
from googleapiclient import discovery

scope = ['https://spreadsheets.google.com/feeds',
    'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', scope)
gc = gspread.authorize(credentials)

service = discovery.build('sheets', 'v4', credentials=credentials)

spreadsheet_body = {
"properties": {
    "title": "xxGoogleAPIMasterTemplatexx"
  }
}

request = service.spreadsheets().create(body=spreadsheet_body)
response = request.execute()

#Changing permissions for the user (from the credentials.json) and then the real user (me)
gc.insert_permission(response['spreadsheetId'], '[email protected]', perm_type='user', role='owner')
gc.insert_permission(response['spreadsheetId'], '[email protected]', perm_type='user', role='owner')

The new spreadsheet is called xxGoogleAPIMasterTemplatexx as I expected (script). Now, I am trying to copy a sheet from one spreadsheet (the original template) to the newly created spreadsheets.

My step now is to first test whether the copy_to_spreadsheet works for specific IDs. I used the following script:

import gspread
from oauth2client.service_account import ServiceAccountCredentials

from pprint import pprint
from googleapiclient import discovery

scope = ['https://spreadsheets.google.com/feeds',
    'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', scope)
gc = gspread.authorize(credentials)

# The ID of the spreadsheet containing the sheet to copy. Everybody has access!!!
spreadsheet_id = '1lw6FVG_gSDseDdseS54jOyXph74k_XfTvnyU2Wqd7yo'  
#sheet = gc.open_by_key(response['spreadsheet_id']).Sheet1

# The ID of the sheet to copy. Everybody has access!!!
sheet_id = 0  

copy_sheet_to_another_spreadsheet_request_body = {
    {
  "destinationSpreadsheetId": "1d8CeUxukBIOUpADE6eBlaksS2ptBjI0vIRpVm8ufEbs"
}
}

request = service.spreadsheets().sheets().copyTo(spreadsheetId=spreadsheet_id, sheetId=sheet_id, body=copy_sheet_to_another_spreadsheet_request_body)
response = request.execute()

and I got this error:

"destinationSpreadsheetId": "1d8CeUxukBIOUpADE6eBlaksS2ptBjI0vIRpVm8ufEbs"
TypeError: unhashable type: 'dict'

I am guessing the ID has to be hashable. After adding this line:

key = frozenset(dict_key.spreadsheet_id)

it still not work.

Please note that for the test purposes I changed the permissions of the files to global:

Tanaike · Accepted Answer · 2017-10-18 08:35:51Z

2

How about a following modification?

I think that the error means that the structure of "destinationSpreadsheetId": "1d8CeUxukBIOUpADE6eBlaksS2ptBjI0vIRpVm8ufEbs" is wrong. When it sees the right side of the document of spreadsheets.sheets.copyTo, the request body is {"destinationSpreadsheetId": ""}. So please try a following modification.

From :

copy_sheet_to_another_spreadsheet_request_body = {
    {
  "destinationSpreadsheetId": "1d8CeUxukBIOUpADE6eBlaksS2ptBjI0vIRpVm8ufEbs"
}
}

To :

copy_sheet_to_another_spreadsheet_request_body = {
  "destinationSpreadsheetId": "1d8CeUxukBIOUpADE6eBlaksS2ptBjI0vIRpVm8ufEbs"
}

If I misunderstand your question, I'm sorry.

Edit :

For the next error, I confirmed following patterns.

When the user who uses this script is the owner of the spreadsheet that you want to copy, of course, the script works fine.
When the user who uses this script is NOT the owner of the spreadsheet that you want to copy,
- If the spreadsheet is not shared, the error of "The caller does not have permission" occurred. This error may be the same to your situation.
- If the spreadsheet is shared as On - Anyone with the link, the script works fine.

From above results, can you confirm the permission of the spreadsheet you want to copy again?

edited Oct 18, 2017 at 8:35

answered Oct 17, 2017 at 22:39

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Datacrawler Over a year ago

I am getting this:

raise HttpError(resp, content, uri=self.uri) googleapiclient.errors.HttpError: <HttpError 403 when requesting https://sheets.googleapis.com/v4/spreadsheets/1lw6FVG_gSDseDdseS54jOyXph74k_XfTvnyU2Wqd7yo/sheets/0:copyTo?alt=json returned "The caller does not have permission">

I am still investigating why. The files are both public. No permissions needed. They were both created using the first script too.

Tanaike Over a year ago

@Apolo Radomer I'm sorry for the inconvenience. Since TypeError: unhashable type: 'dict' disappeared, one error was removed. For the next error, I confirmed some patterns. I updated my answer. Please confirm it.

Collectives™ on Stack Overflow

Copy spreadsheet sheet to a another one with Google API using Python

1 Answer 1

From :

To :

Edit :

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

From :

To :

Edit :

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related