使用Google Sheets API复制工作表如何仅保留数据与格式而非公式？

spreadsheets().sheets().copyTo方法会完整复制工作表的所有内容（包括公式），且易因权限或文档冲突报错。要实现仅复制值和格式，可采用以下两种方案：

方案1：先复制工作表，再批量替换公式为值 #

1. 执行复制操作，获取新工作表ID

   copy_response = spreadsheets().sheets().copyTo(
       spreadsheetId=spreadsheet_id,
       sheetId=sheet_id,
       body={'destination_spreadsheet_id': target_spreadsheet}
   ).execute()
   new_sheet_id = copy_response['sheetId']
   new_sheet_title = copy_response['title']
   

2. 读取复制后工作表的格式化值（即公式计算结果）

   target_range = f"'{new_sheet_title}'!A1:ZZ"
   formatted_values = spreadsheets().values().get(
       spreadsheetId=target_spreadsheet,
       range=target_range,
       valueRenderOption='FORMATTED_VALUE'
   ).execute().get('values', [])
   

3. 将格式化值写回工作表，覆盖原公式

   update_body = {
       'valueInputOption': 'RAW',
       'data': [{
           'range': target_range,
           'values': formatted_values
       }]
   }
   spreadsheets().values().batchUpdate(
       spreadsheetId=target_spreadsheet,
       body=update_body
   ).execute()
   

   此方法保留copyTo复制的格式，仅替换公式为计算后的值。

方案2：手动新建工作表并写入值与格式（规避copyTo报错） #

若copyTo本身报错，可跳过该步骤，直接新建工作表并复制内容：

1. 在目标文档创建新工作表

   create_sheet_req = {
       'requests': [{
           'addSheet': {
               'properties': {'title': '复制的工作表'} # 自定义标题
           }
       }]
   }
   create_response = spreadsheets().batchUpdate(
       spreadsheetId=target_spreadsheet,
       body=create_sheet_req
   ).execute()
   new_sheet_id = create_response['replies'][0]['addSheet']['properties']['sheetId']
   new_sheet_title = '复制的工作表'
   

2. 读取源表的格式化值

   source_range = f"'源工作表名称'!A1:ZZ" # 替换为实际源表标题
   source_values = spreadsheets().values().get(
       spreadsheetId=spreadsheet_id,
       range=source_range,
       valueRenderOption='FORMATTED_VALUE'
   ).execute().get('values', [])
   

3. 将值写入新工作表

   spreadsheets().values().update(
       spreadsheetId=target_spreadsheet,
       range=f"'{new_sheet_title}'!A1",
       valueInputOption='RAW',
       body={'values': source_values}
   ).execute()
   

4. 复制源表格式到新工作表

   # 获取源表单元格格式
   source_format_data = spreadsheets().get(
       spreadsheetId=spreadsheet_id,
       ranges=[source_range],
       fields='sheets(data(rowData(values(userEnteredFormat))))'
   ).execute()
   
   # 构造格式更新请求
   format_updates = []
   sheet_data = source_format_data['sheets'][0]['data'][0]
   for row_idx, row in enumerate(sheet_data.get('rowData', [])):
       if 'values' not in row:
           continue
       for col_idx, cell in enumerate(row['values']):
           if 'userEnteredFormat' in cell:
               format_updates.append({
                   'repeatCell': {
                       'range': {
                           'sheetId': new_sheet_id,
                           'startRowIndex': row_idx,
                           'endRowIndex': row_idx + 1,
                           'startColumnIndex': col_idx,
                           'endColumnIndex': col_idx + 1
                       },
                       'cell': {'userEnteredFormat': cell['userEnteredFormat']},
                       'fields': 'userEnteredFormat'
                   }
               })
   # 执行格式更新
   if format_updates:
       spreadsheets().batchUpdate(
           spreadsheetId=target_spreadsheet,
           body={'requests': format_updates}
       ).execute()
   

关键提示 #

• 确保API账号拥有源文档和目标文档的编辑权限
• 范围A1:ZZ可根据实际数据范围缩小，减少不必要的计算
• 方案1适合copyTo能正常运行但需清除公式的场景；方案2适合copyTo报错的情况

内容的提问来源于stack exchange，提问作者pranav dua