PHP Google Sheets copy sheet from one spreadsheet to another

Question

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.

Tanaike · Accepted Answer · 2024-10-23 11:32:22Z

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:

Method: spreadsheets.sheets.copyTo

Datadimension · Accepted Answer · 2024-10-24 17:32:11Z

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

Collectives™ on Stack Overflow

PHP Google Sheets copy sheet from one spreadsheet to another

2 Answers 2

Note:

Reference:

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Note:

Reference:

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related