Copy paste Google sheet without formulae and just data-CodePudding

I want to copy a sheet from a Google sheet to another Google sheet where I want to keep the data and formatting intact but not the formula. Just want to copy the cell values to another sheet i.e. raw data.

I am using the google sheets api -

spreadsheets().sheets().copyTo(spreadsheetId=spreadsheet_id, sheetId=sheet_id, body={'destination_spreadsheet_id': target_spreadsheet})

but this is copying the formula and throws error

CodePudding user response：

You could use SpreadsheetApp:

const destination = SpreadsheetApp.openById("id1")
const source = SpreadsheetApp.openById("id2")

source.getSheetByName("sheet-name").copyTo(destination)

CodePudding user response：

I believe your goal is as follows.

You want to copy a sheet in a source Spreadsheet to a destination Spreadsheet.
You want to remove the formulas while the cell formats and the cell values are kept.
You want to achieve this using googleapis for python.

In this case, how about the following patterns?

Pattern 1:

In this pattern, your showing script is modified.

service = build("sheets", "v4", credentials=creds) # Please use your client.

srcSpreadsheetId = "###" # Please set source Spreadsheet ID.
srcSheetId = "###" # Please set source sheet ID.
dstSpreadsheetId = "###" # Please set destination Spreadsheet ID.

res = service.spreadsheets().sheets().copyTospreadsheetId=srcSpreadsheetId, sheetId=srcSheetId, body={"destination_spreadsheet_id": dstSpreadsheetId}).execute()
)
service.spreadsheets().batchUpdate(spreadsheetId=dstSpreadsheetId, body={"requests": [{"copyPaste": {"source": {"sheetId": res["sheetId"]},"destination": {"sheetId": res["sheetId"]},"pasteType": "PASTE_VALUES"}}]}).execute()

When this script is run, a sheet in a source Spreadsheet is copied to a destination Spreadsheet. In this case, the formulas are also copied. And, only the values are copied using batchUpdate method. By this, the cell formats and the values are copied without the formulas.

Pattern 2:

In this pattern, the copy process is changed from the above pattern. Because, when the above script is used, if the formulas using the other sheets and Spreadsheet are included in the source sheet, the copied sheet has no values. Unfortunately, from your question, I couldn't confirm this. So, I would like to propose this pattern 2.

service = build("sheets", "v4", credentials=creds) # Please use your client.

srcSpreadsheetId = "###" # Please set source Spreadsheet ID.
srcSheetId = "###" # Please set source sheet ID.
dstSpreadsheetId = "###" # Please set destination Spreadsheet ID.

# 1. Duplicate the source sheet in the source Spreadsheet as a temporal sheet.
newSheetId = "123456789"
service.spreadsheets().batchUpdate(spreadsheetId=srcSpreadsheetId,body={"requests": [{"duplicateSheet": {"sourceSheetId": srcSheetId,"newSheetId": newSheetId}}]}).execute()
time.sleep(3)

# 2. Remove formulas.
service.spreadsheets().batchUpdate(spreadsheetId=srcSpreadsheetId,body={"requests": [{"copyPaste": {"source": {"sheetId": newSheetId},"destination": {"sheetId": newSheetId},"pasteType": "PASTE_VALUES"}}]}).execute()

# 3. Copy the source sheet from the source Spreadsheet to the destination Spreadsheet.
service.spreadsheets().sheets().copyTo(spreadsheetId=srcSpreadsheetId,sheetId=newSheetId,body={"destination_spreadsheet_id": dstSpreadsheetId}).execute()

# 4. Delete temporal sheet from source Spreadsheet.
service.spreadsheets().batchUpdate(spreadsheetId=srcSpreadsheetId,body={"requests": [{"deleteSheet": {"sheetId": newSheetId}}]},
).execute()

When this script is run, the following flow is run.
1. Duplicate the source sheet in the source Spreadsheet as a temporal sheet.
2. Remove formulas.
3. Copy the source sheet from the source Spreadsheet to the destination Spreadsheet.
4. Delete temporal sheet from source Spreadsheet.
If the copied sheet has no value from the formulas, please increase 3 of time.sleep(3).

Pattern 1:

Pattern 2:

References: