PHP-谷歌API复制和粘贴行保持原始格式

Question

我有两个谷歌电子表格文档，MainSpreadsheet和SecondarySpreadSheet。MainSpreadsheet有很多行，其中一些行我需要复制并保持样式格式。因此，我需要从MainSpreadSheet中提取一些特定的行，并使用Google API PHP将它们粘贴到SecondarySpreadSheets中。我试着使用类名Google_Service_Sheets_CopyPasteRequest((，但我没能解决这个问题。你们能帮我复制和粘贴与主电子表格相同的格式风格吗？感谢你们抽出时间。

$newSpreadSheetID = '12345' ;
$mainSpreadSheetID = '54321';

$client = new Google_Client();
$client->setApplicationName('NAME');
$client->setScopes([Google_Service_Sheets::SPREADSHEETS]);
$client->setAccessType('offline');
$client->setAuthConfig('credentials.json');
$service = new Google_Service_Sheets($client);

/**
* Copy from the Main Sheet
*/
$mainFirstSheetID = null;
$worksheetSheets = $service->spreadsheets->get($mainSpreadSheetID)->sheets;
foreach($worksheetSheets as $sheet){
$mainFirstSheetID = $sheet->properties['sheetId'];     
}

$copyRange = new Google_Service_Sheets_GridRange();
$copyRange->setSheetId($mainFirstSheetID);
$copyRange->setStartRowIndex(10);
$copyRange->setEndRowIndex(11);
$copyRange->setStartColumnIndex(0);
$copyRange->setEndColumnIndex(400);
/**
* Paste in the new SHEET
*/
$newSheetID = null;
$worksheetSheets = $service->spreadsheets->get($newSpreadSheetID)->sheets;
foreach($worksheetSheets as $sheet){ 
$newSheetID = $sheet->properties['sheetId'];     
}
$pasteRange = new Google_Service_Sheets_GridRange();
$pasteRange->setSheetId($newSheetID);
$pasteRange->setStartRowIndex(10);
$pasteRange->setEndRowIndex(11);
$pasteRange->setStartColumnIndex(0);
$pasteRange->setEndColumnIndex(400);

$copypasteRequest = new Google_Service_Sheets_CopyPasteRequest();
$copypasteRequest->setSource($copyRange);
$copypasteRequest->setDestination($pasteRange);

$request = new Google_Service_Sheets_Request(); 
$request->setCopyPaste($copypasteRequest);
$batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest();
$batchUpdateRequest->setRequests($request);
$newSpreadsheet = $service->spreadsheets->get($newSpreadSheetID);
$mainSpreadsheet = $service->spreadsheets->get($mainSpreadSheetID);
$finalRowRange = $newSpreadsheet->range;
$finalRowStartPosition = strpos($newSpreadsheet->range,':') + 2;
$finalRow = intval(substr($finalRowRange,$finalRowStartPosition));
if($finalRow <= $pasteRange['startRowIndex']){
$appendDimensionRequest = new Google_Service_Sheets_AppendDimensionRequest();
$appendDimensionRequest->setSheetId($newSheetID);
$appendDimensionRequest->setDimension("ROWS");
$appendDimensionRequest->setLength(1);
$appendRequest = new Google_Service_Sheets_Request(); 
$appendRequest->setAppendDimension($appendDimensionRequest);
$appendEmptyRowBatchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest();
$appendEmptyRowBatchUpdateRequest->setRequests($appendRequest);
$service->spreadsheets->batchUpdate($newSpreadSheetID, $appendEmptyRowBatchUpdateRequest);
}
$service->spreadsheets->batchUpdate($newSpreadSheetID, $batchUpdateRequest);

$service->spreadsheets_values->update(
$newSpreadSheetID,
$finalRow,
$copypasteRequest
);

Answer 1

我相信你的目标和目前的情况如下。

• 您想复制一行"；"主片材"；到电子表格的第一个空行；新片材">
• 不仅要复制值，还要复制单元格格式
• 您希望使用适用于PHP的googleapi来实现这一点
• 您已经能够使用Sheets API获取和放置Google电子表格的值

修改点：

• 不幸的是；CopyPasteRequest；batchUpdate方法的无法从Google电子表格复制到其他Google电子表格。这似乎是当前的规范。

• 为了不仅复制值，而且复制来自谷歌电子表格的单元格格式"；"主片材"；谷歌电子表格"；"新片材"；，我想提出以下流程。

  1. 将源电子表格中的源工作表复制到目标电子表格
  2. 检索目标工作表的第一个空行号
  3. 将具有格式的值从复制的图纸复制到目标图纸。然后，删除复制的工作表

这个流程来自我最近的回答。但这是python脚本。因此，为了将这个流用于PHP，我回答了一个示例脚本。当以上各点反映到脚本中时，它将变为如下所示。

示例脚本：

在这个示例脚本中，使用了$service。因此，请添加授权脚本。并且，请设置运行脚本的变量。

$service = new Google_Service_Sheets($client); // <--- This is from your script.
$copiedRowNumber = 16; // Please set the row number of "MAIN SHEET" that you want to copy.
$sourceSpreadsheetId = "###"; // Please set the source Spreadsheet ID.
$sourceSheetId = "0"; // Please set the source sheet ID.
$destinationSpreadsheetId = "###"; // Please set the destination Spreadsheet ID.
$destinationSheetName = "Página1"; // Please set the destination sheet name.
$destinationSheetId = "0"; // Please set the destination sheet ID.
// 1. Copy the source sheet in the source Spreadsheet to the destination Spreadsheet.
$requestBody = new Google_Service_Sheets_CopySheetToAnotherSpreadsheetRequest(
array("destinationSpreadsheetId" => $destinationSpreadsheetId)
);
$res1 = $service->spreadsheets_sheets->copyTo($sourceSpreadsheetId, $sourceSheetId, $requestBody);
$copiedSheetId = $res1["sheetId"];
// 2. Retrieve 1st empty row number of the destination sheet.
$res2 = $service->spreadsheets_values->get($destinationSpreadsheetId, $destinationSheetName);
$row = count($res2["values"]);
// 3. Copy the values with the format from the copied sheet to the destination sheet. And, delete the copied sheet.
$requests = [
new Google_Service_Sheets_Request([
'copyPaste' => [
'source' => [
'sheetId' => $copiedSheetId,
'startRowIndex' => $copiedRowNumber - 1,
'endRowIndex' => $copiedRowNumber,
],
'destination' => [
'sheetId' => $destinationSheetId,
'startRowIndex' => $row,
'endRowIndex' => $row + 1
],
'pasteType' => 'PASTE_NORMAL',
],
]),
new Google_Service_Sheets_Request([
'deleteSheet' => ['sheetId' => $copiedSheetId]
])
];
$batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest(['requests' => $requests]);
$res3 = $service->spreadsheets->batchUpdate($destinationSpreadsheetId, $batchUpdateRequest);

注：

• 在这个示例脚本中，它假设您的$service = new Google_Service_Sheets($client);可以用于使用上面的脚本。请小心

参考文献：

• 方法：spreadsheets.sheets.copyTo
• CopyPasteRequest
• DeleteSheet请求