Sorting sheets in Google Drive Spreadsheet using PHP API

Question

I have a SpreadSheet on Google Drive and I'm updating the content using PHP API (https://developers.google.com/sheets/api/guides/values).

I store here reports from my projects. Each project has its sheet.

When the project has no sheet (new project), I create a new sheet for this project called by the name of a new project.

The sheets is created as a last one.

This is my current code to create a new Sheet:

$body = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest(
  ['requests' => ['addSheet' => ['properties' => ['title' => $project_name]]]]
);

$result = $service->spreadsheets->batchUpdate($spreadsheetId, $body);

I would like to have sheets sorted alphabetically. Excluding the first sheet, where are some global statistics.

Is there any way to change the order of the sheets using Google SpreadSheet API?

ziganotschka · Accepted Answer · 2019-10-11 12:12:25Z

1

This would be the workflow:

Each time after creating a new sheet use the method spreadsheets.get and retrieve the sheets of the spreadsheet
Retrieve the ids and titles of the sheets, pass the ids into an array and sort the position of the ids as a function of title, as performed here
Perform an UpdateSheetPropertiesRequest, specifying the SheetProperty "index" - which will move the sheet to a position corresponding to the position of the sheetId in the alpahabetically sorted array.

answered Oct 11, 2019 at 12:12

ziganotschka

26.9k2 gold badges18 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Petr · Accepted Answer · 2019-10-11 12:56:31Z

0

I just found the solution ;-)

$requests = [
    new Google_Service_Sheets_Request([
        'updateSheetProperties' => [
            'fields' => 'index',
            'properties' => [
                'sheetId' => $sheet_id,
                'index' => $i,
            ],
        ],
    ])
];
$batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
    'requests' => $requests
]);
$result = $service->spreadsheets->batchUpdate($spreadsheetId, $batchUpdateRequest);

answered Oct 11, 2019 at 12:56

Petr

1117 bronze badges

1 Comment

ziganotschka Over a year ago

Yes, that's it!

Collectives™ on Stack Overflow

Sorting sheets in Google Drive Spreadsheet using PHP API

2 Answers 2

This would be the workflow:

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

This would be the workflow:

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related