Skip to content

Instantly share code, notes, and snippets.

@weitzman
Created May 14, 2022 15:17
Show Gist options
  • Save weitzman/e4177c676bd3e431aea159b6379f7d65 to your computer and use it in GitHub Desktop.
Save weitzman/e4177c676bd3e431aea159b6379f7d65 to your computer and use it in GitHub Desktop.
Get Assignees from GitLab API, format data and post to Google Sheets
#!/usr/bin/env php
<?php
require dirname(__DIR__) . '/vendor/autoload.php';
if (php_sapi_name() != 'cli') {
throw new Exception('This application must be run on the command line.');
}
$path_credentials = getenv('PUBLISH_CREDITS_CREDENTIALS') ?: 'credentials.json';
$path_token = getenv('PUBLISH_CREDITS_TOKEN') ?: 'token.json';
$rows = get();
$response = post($rows);
/**
* Returns an authorized API client.
*
* @return Google_Client
* The authorized client object.
*/
function getClient() {
global $path_token, $path_credentials;
$client = new Google_Client();
$client->setApplicationName('Google Sheets API PHP Quickstart');
$client->setScopes(Google_Service_Sheets::SPREADSHEETS);
$client->setAuthConfig($path_credentials);
$client->setAccessType('offline');
$client->setPrompt('select_account consent');
// Load previously authorized token from a file, if it exists.
// The file token.json stores the user's access and refresh tokens, and is
// created automatically when the authorization flow completes for the first
// time.
$tokenPath = $path_token;
if (file_exists($tokenPath)) {
$accessToken = json_decode(file_get_contents($tokenPath), true);
$client->setAccessToken($accessToken);
}
// If there is no previous token or it's expired.
if ($client->isAccessTokenExpired()) {
// Refresh the token if possible, else fetch a new one.
if ($client->getRefreshToken()) {
$client->fetchAccessTokenWithRefreshToken($client->getRefreshToken());
} else {
// Request authorization from the user.
$authUrl = $client->createAuthUrl();
printf("Open the following link in your browser:\n%s\n", $authUrl);
print 'Enter verification code: ';
$authCode = trim(fgets(STDIN));
// Exchange authorization code for an access token.
$accessToken = $client->fetchAccessTokenWithAuthCode($authCode);
$client->setAccessToken($accessToken);
// Check to see if there was an error.
if (array_key_exists('error', $accessToken)) {
throw new Exception(join(', ', $accessToken));
}
}
// Save the token to a file.
if (!file_exists(dirname($tokenPath))) {
mkdir(dirname($tokenPath), 0700, true);
}
file_put_contents($tokenPath, json_encode($client->getAccessToken()));
}
return $client;
}
/**
* Get data from Gitlab and format into rows.
*
* @return array
*/
function get() {
// Uses personal token with grant 'api' scope: https://gitlab.com/profile/personal_access_tokens
if (!$token = getenv('GITLAB_TOKEN')) {
exit('Missing token');
}
$config = [
'headers' => ['Authorization' => 'Bearer '. $token],
'http_errors' => true,
// 'debug' => true,
'base_uri' => 'https://gitlab.com/api/v4/',
];
$client = new \GuzzleHttp\Client($config);
$drupalspoons = 7657176;
$assignees = [];
// @todo Add pagination.
$response = $client->get("groups/$drupalspoons/merge_requests?assignee_id=Any&state=merged&order_by=updated_at");
$rowsM = format_rows($response, 'MR');
$response = $client->get("groups/$drupalspoons/issues?assignee_id=Any&state=closed&order_by=updated_at");
$rowsI = format_rows($response, 'Issue');
$rows = array_merge([['ID', 'Type', 'Issue', 'Credit1', 'Credit2']], $rowsM, $rowsI);
if (count($rows) <= 1) {
throw new Exception('Fetch data failed');
}
return $rows;
}
/**
* Turn a Gitlab response into rows for a spreadsheet.
*
* @param \Psr\Http\Message\ResponseInterface $response
* @param $type
*
* @return array
*/
function format_rows(\Psr\Http\Message\ResponseInterface $response, $type) {
$body = $response->getBody();
$json = json_decode($body);
foreach ($json as $issue) {
foreach ($issue->assignees as $assignee) {
$assignees[] = "=HYPERLINK(\"$assignee->web_url\", \"$assignee->name\")";
}
$title = str_replace('"', "'", $issue->title);
$row = [
$issue->id,
$type,
"=HYPERLINK(\"{$issue->web_url}\", \"$title\")",
];
$rows[] = array_merge($row, $assignees);
unset($assignees);
}
return $rows;
}
/**
* Post rows to the Google sheet.
*
* @param array $rows
*
* @throws \Exception
*/
function post(array $rows) {
// Send data to GSheets.
$client = getClient();
$service = new Google_Service_Sheets($client);
$spreadsheetId = '1m14yI71qwWpOS1WNXmF5GG6TbcuQlIlCUKT9tIob5F4';
$range = 'Credits!A1:F';
$postBody = new Google_Service_Sheets_ClearValuesRequest();
$service->spreadsheets_values->clear($spreadsheetId, $range, $postBody);
$valueRange = new Google_Service_Sheets_ValueRange();
$valueRange->setValues($rows);
$optParams = ['valueInputOption' => 'USER_ENTERED'];
return $service->spreadsheets_values->update($spreadsheetId, $range, $valueRange, $optParams);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment