phpgoogle-sheets-api

PHP Google Sheets copy sheet from one spreadsheet to another


This needs to be using Google Sheets API and PHP I have a singleton wrapper class (lets face it, most Google PHP apis need them) of which an instance in my client class is

$gsheets

I have had this working previously and still demonstrating I having API access I create a new Spreadsheet by running:

$newspreadsheetTitle="ABC123456";
$newdocument = $gsheets->newSpreadsheetDocument($newspreadsheetTitle);

Which calls my own wrapper class method:

  public function newSpreadsheetDocument($title) {
    $newspread = new Google_Service_Sheets_Spreadsheet([
    'properties' => [
        'title' => $title
    ]
    ]);
    $newspread = $this->service->spreadsheets->create($newspread);
    $id = $newspread->getSpreadsheetId();
    return ["ID" => $newspread->getSpreadsheetId(), "url" => $newspread->getSpreadsheetUrl()];
  }

At this point the new spreadsheet is created titled ABC123456 and returns the expected ID and url, accessable by pasting the link into address bar on browser, with the returned ID. This demonstrates that

$this->service

is a fully functioning client of Google Sheets in my wrapper class and creates a spreadsheet document named $spreadsheetRef.

So now my question:

I then wish to copy a content (a whole tab/sheet) from this template to this newly created spreadsheet so I call

  $gsheets->copySheetFromTo($POtemplateID, $newdocument["ID"], 0);

to my wrapper instance method

  public function copySheetFromTo($sourceSpreadsheetFileId, $destinationSpreadsheetFileId, $sourceTabRef) {
    $requestBody = new Google_Service_Sheets_CopySheetToAnotherSpreadsheetRequest();
    $requestBody->setDestinationSpreadsheetId($destinationSpreadsheetFileId);
    $response = $this->service->spreadsheets_sheets->copyTo($sourceSpreadsheetFileId, $sourceTabRef, $requestBody);

    /*
        $request = new Google_Service_Sheets_CopySheetToAnotherSpreadsheetRequest([
            "destinationSpreadsheetId" => $toFileID
        ]);
        $this->service->spreadsheets_sheets->copyTo($fromFileID, $fromTabName, $request);
    */
    return $response;
  }

I have tried several permuations eg

      $gsheets->copySheetFromTo($POtemplateID, $newdocument["ID"], 0);

error:

The sheet (0) does not exist.

and

      $gsheets->copySheetFromTo($POtemplateID, $newdocument["ID"], 1);

error:

The sheet (1) does not exist.

and

  $gsheets->copySheetFromTo($POtemplateID, $newdocument["ID"], "template_sheet");//the name of the sheet tab to copy

error

Invalid value at 'sheet_id' (TYPE_INT32), "template_sheet"

Please could somebody advise what parameters are incorrect here.


Solution

  • I solved this by looking up the sheetId - I had assumed it was the tab number because previously this worked. A small change to code is needed, namely the line

    $sourceSheetID = $this->service->spreadsheets->get($sourceSpreadsheetFileId)->getSheets()[0]->getProperties()->sheetId;
    

    in this method

      public function copySheetFromTo($sourceSpreadsheetFileId, $destinationSpreadsheetFileId, $sourceTabNumber) {
        $sourceSheetID = $this->service->spreadsheets->get($sourceSpreadsheetFileId)->getSheets()[0]->getProperties()->sheetId;
        $requestBody = new Google_Service_Sheets_CopySheetToAnotherSpreadsheetRequest();
        $requestBody->setDestinationSpreadsheetId($destinationSpreadsheetFileId);
        $response = $this->service->spreadsheets_sheets->copyTo($sourceSpreadsheetFileId, $sourceSheetID, $requestBody);
        return $response;
      }