Get Assignees from GitLab API, format data and post to Google Sheets

publish_credits.php

#!/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);
}