Question 412569 in PHP Question posted in
Official manual can be found here.

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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;
  }

  2. 0

    About your showing script, $sourceTabRef is required to be the sheet ID of the source sheet in the source Spreadsheet. Ref

    But, from your error messages The sheet (0) does not exist. and The sheet (1) does not exist., I’m worried that the sheet ID of the sheet you want to copy might be different from 0 and 1. So, please confirm it again.

    Also, the text template_sheet cannot also be used as the sheet ID. Please also be careful about this.

    You can see the sheet ID in the URL of the Spreadsheet at this official document.

    https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit?gid=SHEET_ID#gid=SHEET_ID

    Note:

    • I think that if your client $this->service can use Sheet API and access the Spreadsheets, your script copySheetFromTo works. When I tested it, I confirmed that the script worked using a valid sheet ID to $sourceTabRef.

    Reference:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search