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?

Answer 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.

Answer 2

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