Search code examples
pythongoogle-apps-scriptgoogle-apigspread

Save google spreadsheet as new

UPDATE shown at the end of my question.

I am able to create a new google spreadsheet using Python and gspread:

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

gc.insert_permission(response['spreadsheetId'], 'xxxxx@zzzz-yyyyyy.iam.gserviceaccount.com', perm_type='user', role='owner')
gc.insert_permission(response['spreadsheetId'], 'xxxx.xxxx@gmail.com', perm_type='user', role='owner')

which works fine.

My target is to use a template google spreadsheet, copy it and then edit the copy (some cells --> which is easy and I can confirm that I have done this part) and rename it.

What I am trying to do is either:

  • Copy an existing spreadsheet. Then I will just need to know the name or the id of the copied spreadsheet.
  • Open an existing spreadsheet and save as new.

  • Add the spreadsheet body in the script above. More specifically, edit this part:

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

in order to make it include all the headers and values in the cells. Is that possible? How can I see what the properties are from an existing spreadsheet?

I have not found something that works. I have only found how to copy a sheet (tab) from one google spreadsheet to another.

PS: I am using API V4.

UPDATED:

I followed the first answer I got in this question. So, I have created a script that copies a sheet into another spreadsheet. 

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)

# The ID of the spreadsheet containing the sheet to copy.
spreadsheet_id = 'xxxxxx'   

# The ID of the sheet to copy.
sheet_id = 730266781  # TODO: Update placeholder value.

copy_sheet_to_another_spreadsheet_request_body = {
    # The ID of the spreadsheet to copy the sheet to.
    'destination_spreadsheet_id': 'yyyy',  

    # TODO: Add desired entries to the request body.
}

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

# TODO: Change code below to process the `response` dict:
pprint(response)

But I was getting this error:

https://sheets.googleapis.com/v4/spreadsheets/xxxxxx/sheets/730266781:copyTo?alt=json returned "The caller does not have permission">

So, I first tried to grant access to the user mentioned in my credentials.json file (xxxx-yyyy@bbbbb-182311.iam.gserviceaccount.com)

Then I also tried using this line:

gc.insert_permission(response['spreadsheetId'], 'xxxx-yyyy@bbbbb-182311.iam.gserviceaccount.com', perm_type='user', role='owner')

I was not able to find a solution on that and I skipped this part. I made the template public.

Solution

  • How about the following workarounds? I think that there are 2 patterns.

    Pattern 1 :

    1. Create new spreadsheet with { "properties": { "title": "xxGoogleAPIMasterTemplatexx" } } using service.spreadsheets().create().
      • File ID and filename can be returned.
    2. Copy the template spreadsheet to the new spreadsheet using service.spreadsheets().sheets().copyTo().

    In this case, only Sheets API is used.

    Pattern 2 :

    1. Copy the template spreadsheet with the title of {"name": "xxGoogleAPIMasterTemplatexx"} using service.files().copy().
      • File ID and filename can be returned.

    In this case, only Drive API is used.

    About additional questions :

    1. Q: in order to make it include all the headers and values in the cells. Is that possible?
      • A: This can be achieved by copying the template sheet. This is possible for both patterns. If you want to copy data from other spreadsheet to copied spreadsheet, you can use values.get and values.update.
    2. Q: How can I see what the properties are from an existing spreadsheet?
      • A: You can retrieve the properties using service.spreadsheets().get() as fields=properties.

    References :

    You can see the sample script at these references.

    If I misunderstand your question, I'm sorry.

    Edit :

    In order to delete sheet with id=0, you can use following script.

    batch_update_spreadsheet_request_body = {
  "requests": [{
    "deleteSheet": {
      "sheetId": 0
    }
  }]
}
service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id, body=batch_update_spreadsheet_request_body)

    Reference :