Duplicating values and formulas to a new sheet with gspread impossible?

Question

this has stumped google search and ChatGPT, Theres actually no why to copy all FORMULAS from one existing sheet to create another one with gspread? I literally have to download this sheet with openpyxl, then create a new sheet

> # Copy the original worksheet's data and formatting to the new worksheet from copy import copy
> 
> for row in default_sheet.rows:
>     for cell in row:
>         new_cell = new_sheet.cell(row=cell.row, column=cell.column,
>                 value= cell.value)
>         if cell.has_style:
>             new_cell.font = copy(cell.font)
>             new_cell.border = copy(cell.border)
>             new_cell.fill = copy(cell.fill)
>             new_cell.number_format = copy(cell.number_format)
>             new_cell.protection = copy(cell.protection)
>             new_cell.alignment = copy(cell.alignment)
>             new_cell.value = cell.value

And then save this sheet and REUPLOAD into my google drive because gspread does not have the method get_all_values or get_formula or any of the sort Is this true? Am i dense? How do I accomplish this without the round about way, I can duplicate a sheet but only its values with:

# Set up the API client and authorize it with your credentials
from googleapiclient.discovery import build

service = build('sheets', 'v4', credentials=gdrive_credentials)

# Open the spreadsheet
spreadsheet = gdrive_client.open("copy")

# Get the original worksheet
original_worksheet = spreadsheet.worksheet("12/13")

# Get the data from the original worksheet
original_data = original_worksheet.get_all_values()

# Create a new worksheet and give it a name
new_worksheet = spreadsheet.add_worksheet(title="1/1", rows=len(original_data), cols=len(original_data[0]))

# Set the data in the new worksheet
new_worksheet.update('A1', original_data)

I've also been trying this method which I have no idea what im doing here:

from google.oauth2.service_account import Credentials
from googleapiclient.discovery import build
from oauth2client.service_account import ServiceAccountCredentials


# Use the credentials to create a client object
scope = ['https://www.googleapis.com/auth/spreadsheets']
creds = ServiceAccountCredentials.from_json_keyfile_name('/Users/k/Downloads/secret-elment-37.json', scope)
service = build('sheets', 'v4', credentials=creds)


spreadsheet_id = '16yQw4DjuJpzGgdz3Jk'
mySheetId = '153016'
request_body = {
    'requests': [
        {
            'duplicateSheet': {
                'sourceSheetID': mySheetId,
                'newSheetName': 'TargetSheet'
                }
            }
        ]
    }

response = service.spreadsheet().batchUpdate(spreadsheetId = spreadsheet_id,body=request_body).execute()

bu11d0zer · Accepted Answer · 2023-05-04 22:57:52Z

0

Use worksheet.copy_range(src_range, dst_range, paste_type='PASTE_FORMULA').

For more info: https://docs.gspread.org/en/latest/api/models/worksheet.html.

Hmm, this doesn't allow pasting to a DIFFERENT sheet. For that you'd have to use duplicate. Does that do what you want?

worksheet.duplicate(insert_sheet_index=None, new_sheet_id=None, new_sheet_name=None)

answered May 4, 2023 at 22:57

bu11d0zer

4514 silver badges13 bronze badges

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

Collectives™ on Stack Overflow

Duplicating values and formulas to a new sheet with gspread impossible?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related