PHP Google Sheets API v4从前一行获取所有格式(条件，日期等)并应用于新行插入的最有效方法? [英] PHP Google Sheets API v4 Most efficient way to get all formatting - conditional, dates, etc - from preceeding row and apply to a new row insert?

问题描述

大家好！

插入新数据行并应用前一行样式的最佳方法是什么?(我们正在迁移PHP代码以使用Google Sheets api v4类)

我目前正在努力解决此问题，并针对电子表格-> batchUpdate使用Google_Service_Sheets_BatchUpdateSpreadsheetRequest-我可以在代码中应用格式更改.但是，我不太确定是否可以循环使用相应的get请求，以获取现有格式并使用这些格式?

用旧钱，我们完全不必担心这一点，因为我们只是将新行的数据添加到listFeed-> insert函数中，该函数似乎在照顾格式化方面本身.

但是，现在的问题是，我是否需要遍历范围内的每个单元格以获取格式，然后一次将该单元格应用于新行，还是有更好的方法(例如，现有的行级操作)?

解决方案

我通常不会回答我自己的问题，但是鉴于Google将于2020年3月关闭v3 Sheets API，我怀疑其他人可能也会遇到此问题./p>

正如我在针对原始问题的评论中提到的那样，v4 API中没有访问单元格格式的现有方法.因此，无法选择遍历一个范围并将格式应用于另一范围.

我的解决方法是复制/粘贴上一行(默认的PASTE_NORMAL将复制所有值，公式，格式并合并

我希望这将来对某人有用.

Good day folks!

What is the best way to insert a new row of data and apply the styling from the preceding row? (We are migrating our PHP code to use the Google Sheets api v4 classes)

I am currently working my way through this and using Google_Service_Sheets_BatchUpdateSpreadsheetRequest against the spreadsheets->batchUpdate I can apply format changes in code. However I'm not too sure on the appropriate get request which I could cycle through in order to pick up the existing formats and use these?

In old money we didn't have to worry about this at all as we just added the new row's data against the listFeed->insert function which seemed to take care of the formatting side itself.

However the question now is do I need to cycle through every cell within a range getting the formatting and then applying that a cell at a time to the new row or is there a better way (e.g. an existing row level operation)?

解决方案

I wouldn't normally answer my own question but given that Google are closing off the v3 Sheets API in March 2020 I suspect others may come across this problem.

As I have mentioned in comments against the original question there is no existing means in the v4 API of accessing Cell Formats. Therefore there is no option to loop through one range and apply formats to another.

My workaround solution is to copy / paste the preceding row (the default PASTE_NORMAL will copy all values, formulas, formats, and merges https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#pastetype) and then overwrite the newly pasted row with the required values. This gives me the required values with the existing formatting in the new row:-

    $sheetId = null;
    $worksheetSheets = $this->spreadsheetService->spreadsheets->get($this->spreadsheetId)->sheets;
    foreach($worksheetSheets as $sheet){
        $sheetTitle = $sheet->properties['title'];
        if ($sheetTitle === $this->worksheetTitle){
            $sheetId = $sheet->properties['sheetId'];     
            break;
        }
    }

    $copyRange = new Google_Service_Sheets_GridRange();
    $copyRange->setSheetId($sheetId);
    $copyRange->setStartRowIndex($nextRow - 2);
    $copyRange->setEndRowIndex($nextRow - 1);
    $copyRange->setStartColumnIndex(0);
    $copyRange->setEndColumnIndex(400);

    $pasteRange = new Google_Service_Sheets_GridRange();
    $pasteRange->setSheetId($sheetId);
    $pasteRange->setStartRowIndex($nextRow - 1);
    $pasteRange->setEndRowIndex($nextRow);
    $pasteRange->setStartColumnIndex(0);
    $pasteRange->setEndColumnIndex(400);

    $copypasteRequest = new Google_Service_Sheets_CopyPasteRequest();

    $copypasteRequest->setSource($copyRange);
    $copypasteRequest->setDestination($pasteRange);
    //$copypasteRequest->pasteType(CopyPasteType.PASTE_NORMAL);

    $request = new Google_Service_Sheets_Request(); 
    $request-> setCopyPaste($copypasteRequest);

    $batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest();
    $batchUpdateRequest->setRequests($request);

    // Need to check if sheet has an existing empty row to paste into
    $finalRowRange = $this->worksheet['range'];
    $finalRowStartPosition = strpos($this->worksheet['range'],':') + 2;
    $finalRow = intval(substr($finalRowRange,$finalRowStartPosition));

    if($finalRow <= $pasteRange['startRowIndex']){ // startRowIndex is a zero based array range, i.e. 348 actually corresponds to row 349 on sheet.
        $appendDimensionRequest = new Google_Service_Sheets_AppendDimensionRequest();
        $appendDimensionRequest->setSheetId($sheetId);
        $appendDimensionRequest->setDimension("ROWS");
        $appendDimensionRequest->setLength(1);
        $appendRequest = new Google_Service_Sheets_Request(); 
        $appendRequest->setAppendDimension($appendDimensionRequest);
        $appendEmptyRowBatchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest();
        $appendEmptyRowBatchUpdateRequest->setRequests($appendRequest);
         $this->spreadsheetService->spreadsheets->batchUpdate($this->spreadsheetId, $appendEmptyRowBatchUpdateRequest);
    }
$this->spreadsheetService->spreadsheets->batchUpdate($this->spreadsheetId, $batchUpdateRequest);

    // The actual data values insert
    $this->spreadsheetService->spreadsheets_values->update(
       $this->spreadsheetId,
       $updateRange,
       $valueRange,
       $conf
    );

I hope this may be of some use to someone in future.

这篇关于PHP Google Sheets API v4从前一行获取所有格式(条件，日期等)并应用于新行插入的最有效方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！