get_plugin_settings(); $auth_options = get_option(self::ALPHAGF_GS_TOKEN_DATA, []); if (empty($auth_options['refresh_token']) || empty($settings['client_id']) || empty($settings['client_secret'])) { return false; } $auth_url = 'https://oauth2.googleapis.com/token'; $args = [ 'client_id' => $settings['client_id'], 'client_secret' => $settings['client_secret'] ]; if ($grant_type === 'refresh_token') { $args['grant_type'] = 'refresh_token'; $args['refresh_token'] = $auth_options['refresh_token']; } $postargs = array( 'body' => $args, 'timeout' => 45, 'sslverify' => true, ); $response = wp_remote_post($auth_url, $postargs); // A network hiccup shouldn't mark the connection as broken; just fail this request. if (is_wp_error($response)) { return false; } $authObj = json_decode(wp_remote_retrieve_body($response), true); if (! empty($authObj['access_token'])) { $access = $auth_options; $access['access_token'] = $authObj['access_token']; $access['expires_in'] = time() + absint(rgar($authObj, 'expires_in', 3600)); $access['update_from'] = 'generate with refresh'; update_option(self::ALPHAGF_GS_TOKEN_DATA, $access); update_option(self::ALPHAGF_GS_AUTH_VERIFIED, 1); return $authObj['access_token']; } update_option(self::ALPHAGF_GS_AUTH_VERIFIED, 0); return false; } public static function authenticate($code) { if (empty($code)) { return false; } $client_options = ALPHAGF_Google_Sheet::get_instance()->get_plugin_settings(); $client_id = rgar($client_options, 'client_id'); $client_secret = rgar($client_options, 'client_secret'); $redirect_uri = rgar($client_options, 'redirect_uri') ? $client_options['redirect_uri'] : admin_url('admin.php?page=gf_settings&subview=alphagf_google_sheet'); $response = wp_remote_post('https://oauth2.googleapis.com/token', [ 'body' => [ 'code' => $code, 'client_id' => $client_id, 'client_secret' => $client_secret, 'redirect_uri' => $redirect_uri, 'grant_type' => 'authorization_code' ] ]); if (is_wp_error($response)) { return false; } $response_code = wp_remote_retrieve_response_code($response); if ($response_code !== 200) { update_option(self::ALPHAGF_GS_AUTH_VERIFIED, 0); return false; } $result = json_decode(wp_remote_retrieve_body($response), true); if (! empty($result['access_token'])) { $access = []; if (isset($result['access_token'])) { $access['access_token'] = $result['access_token']; $access['refresh_token'] = rgar($result, 'refresh_token'); $access['expires_in'] = time() + absint(rgar($result, 'expires_in', 3600)); $access_token = true; $access['connected_at'] = self::get_connected_time(); } update_option(self::ALPHAGF_GS_TOKEN_DATA, $access); update_option(self::ALPHAGF_GS_AUTH_VERIFIED, 1); } else { update_option(self::ALPHAGF_GS_AUTH_VERIFIED, 0); $access_token = false; } if ($access_token) { self::get_auth_account_info($result['access_token']); } return $access_token; } public static function get_connected_time() { $timezone = wp_timezone(); $date = new \DateTime('@' . time()); $date->setTimezone($timezone); $formattedDate = $date->format('F j, Y h:i:s A'); return $formattedDate; } public static function get_auth_account_info($access_token) { $auth_account = get_option(self::ALPHAGF_GS_AUTH_ACCOUNT, []); if (!empty($auth_account)) { return $auth_account; } if (empty($access_token)) { return false; } $url = 'https://www.googleapis.com/oauth2/v3/userinfo'; $response = wp_remote_get( $url, [ 'headers' => [ 'Authorization' => 'Bearer ' . $access_token, 'Accept' => 'application/json', ] ] ); if (is_wp_error($response)) { return $response->get_error_message(); } $response_code = wp_remote_retrieve_response_code($response); if (200 !== $response_code) { return false; } $body = wp_remote_retrieve_body($response); $result = json_decode($body, true); $user_data = array( 'name' => isset($result['name']) ? sanitize_text_field($result['name']) : '', 'picture' => isset($result['picture']) ? esc_url_raw($result['picture']) : '', 'email' => isset($result['email']) ? sanitize_email($result['email']) : '', 'time' => self::get_connected_time() ); update_option(self::ALPHAGF_GS_AUTH_ACCOUNT, $user_data); return $user_data; } public static function revoke_token($access_token) { if (empty($access_token)) { return false; } $revoke_url = 'https://oauth2.googleapis.com/revoke'; $response = wp_remote_post($revoke_url, [ 'body' => [ 'token' => $access_token, ], 'timeout' => 15, ]); if (is_wp_error($response)) { return false; } $response_code = wp_remote_retrieve_response_code($response); if (200 === $response_code || 400 === $response_code) { // Remove the token from the WordPress database delete_option(self::ALPHAGF_GS_TOKEN_DATA); delete_option(self::ALPHAGF_GS_AUTH_ACCOUNT); delete_option(self::ALPHAGF_GS_AUTH_VERIFIED); delete_option(self::ALPHAGF_GS_SHEET_LIST); delete_option(self::ALPHAGF_GS_SHEET_NAMES); delete_transient(self::ALPHAGF_GS_SHEET_HEADERS); $settings = get_option('gravityformsaddon_alphagf_google_sheet_settings'); if (is_array($settings)) { unset($settings['client_token']); update_option('gravityformsaddon_alphagf_google_sheet_settings', $settings); } return true; } return false; } public static function make_request($type, $method = 'GET', $uri = '', $params = null) { $access_token = self::get_access_token(); if ($type == 'drive') { // setup the request $url = 'https://www.googleapis.com' . $uri; } else { $url = 'https://sheets.googleapis.com/v4/spreadsheets' . $uri; } $headers = array( 'content-type' => 'application/json', 'Authorization' => 'Bearer ' . $access_token, ); // make the request $req_args = array( 'method' => $method, 'headers' => $headers, 'sslverify' => true, ); switch ($method) { case 'GET': if (! empty($params)) { $querystring = urldecode(http_build_query($params)); // append a query string to the url $url = $url . '?' . $querystring; } break; case 'POST': if (! empty($params)) { $req_args['body'] = json_encode(array('values' => $params)); } break; } // make the remote request $result = wp_remote_request($url, $req_args); if (is_wp_error($result)) { return $result; } $response = json_decode(wp_remote_retrieve_body($result)); $code = (int) wp_remote_retrieve_response_code($result); if ($code < 200 || $code >= 300) { $message = isset($response->error->message) ? $response->error->message : sprintf('Unexpected response code %d from Google.', $code); return new WP_Error($code, $message); } return $response; } /** * Build an encoded A1 range for a sheet. Sheet names are quoted so names with spaces or symbols work. * * @param string $sheet_name The sheet (tab) name. * @param string $cells Optional cell range, e.g. "1:1" or "A1". * * @return string URL-path safe range. */ public static function build_range($sheet_name, $cells = '') { $range = "'" . str_replace("'", "''", (string) $sheet_name) . "'"; if ('' !== $cells) { $range .= '!' . $cells; } return rawurlencode($range); } public static function get_spreadsheet_list() { $access_token = self::get_access_token(); if (empty($access_token)) { return []; } $files = []; $page_token = ''; // Drive returns results in pages, keep going so every spreadsheet can be picked. do { $query = array( 'q' => "mimeType='application/vnd.google-apps.spreadsheet' and trashed=false", 'fields' => 'nextPageToken, files(id, name)', 'pageSize' => 1000, 'orderBy' => 'name', ); if ($page_token) { $query['pageToken'] = $page_token; } $response = wp_remote_get(add_query_arg(array_map('rawurlencode', $query), 'https://www.googleapis.com/drive/v3/files'), array( 'headers' => array( 'Authorization' => 'Bearer ' . $access_token, ), 'timeout' => 45, )); if (is_wp_error($response) || 200 !== (int) wp_remote_retrieve_response_code($response)) { break; } $data = json_decode(wp_remote_retrieve_body($response), true); $files = array_merge($files, is_array(rgar($data, 'files')) ? $data['files'] : []); $page_token = (string) rgar($data, 'nextPageToken'); } while ($page_token && count($files) < 10000); update_option(self::ALPHAGF_GS_SHEET_LIST, $files); return $files; } public static function get_individual_sheet_names($spreadsheet_id) { if (empty($spreadsheet_id)) { return []; } $cached_sheets = get_option(self::ALPHAGF_GS_SHEET_NAMES, []); if (empty($cached_sheets[$spreadsheet_id])) { $sheets = []; $response = self::make_request('sheets', 'GET', '/' . rawurlencode($spreadsheet_id), array('includeGridData' => 'false')); if (is_wp_error($response)) { return []; } if (is_object($response)) { if (isset($response->sheets)) { foreach ($response->sheets as $sheet) { $sheets[] = array( 'label' => $sheet->properties->title, 'id' => $sheet->properties->title, ); } $cached_sheets[$spreadsheet_id] = $sheets; update_option(self::ALPHAGF_GS_SHEET_NAMES, $cached_sheets); } } } else { $sheets = $cached_sheets[$spreadsheet_id]; } return $sheets; } public static function get_spreadsheet_headers($sheet_id, $sheet_name) { if (empty($sheet_name) || empty($sheet_id)) { return array(); } $sheet_name = (string) $sheet_name; $cached_headers = get_transient(self::ALPHAGF_GS_SHEET_HEADERS); $cached_headers = is_array($cached_headers) ? $cached_headers : array(); if (empty($cached_headers[$sheet_id][$sheet_name])) { $headers = array(); $response = self::make_request('sheets', 'GET', '/' . rawurlencode($sheet_id) . '/values/' . self::build_range($sheet_name, '1:1')); if (is_wp_error($response)) { return array(); } if (is_object($response)) { if (isset($response->values[0])) { foreach ($response->values[0] as $key => $name) { $headers[] = array( 'label' => $name, 'id' => $key, ); } $cached_headers[$sheet_id][$sheet_name] = $headers; set_transient(self::ALPHAGF_GS_SHEET_HEADERS, $cached_headers, 60 * 60 * 60); } } } else { $headers = $cached_headers[$sheet_id][$sheet_name]; } return $headers; } public static function set_spreadsheet_headers($sheet_id, $sheet_name, $headers_array) { $range = self::build_range($sheet_name, '1:1'); $access_token = self::get_access_token(); $sheet_id = rawurlencode($sheet_id); $url = "https://sheets.googleapis.com/v4/spreadsheets/{$sheet_id}/values/{$range}?valueInputOption=RAW"; $args = array( 'method' => 'PUT', 'headers' => array( 'Authorization' => 'Bearer ' . $access_token, 'Content-Type' => 'application/json', ), 'body' => json_encode(array( 'values' => array($headers_array) // Must be a nested array: [ ["Col1", "Col2"] ] )), 'timeout' => 15, ); $response = wp_remote_request($url, $args); if (is_wp_error($response)) { return 'Error: ' . $response->get_error_message(); } $code = wp_remote_retrieve_response_code($response); if ($code === 200) { // The cached header list is stale now. delete_transient(self::ALPHAGF_GS_SHEET_HEADERS); return true; // Headers successfully set } return false; } public static function is_spreadsheet_header_exists($spreadsheet_id, $sheet_name) { if (empty($spreadsheet_id) || empty($sheet_name)) { return false; } $access_token = self::get_access_token(); $range = self::build_range($sheet_name, '1:1'); $url = 'https://sheets.googleapis.com/v4/spreadsheets/' . rawurlencode($spreadsheet_id) . "/values/{$range}"; $args = array( 'headers' => array( 'Authorization' => 'Bearer ' . $access_token, 'Content-Type' => 'application/json', ), 'timeout' => 45, ); $response = wp_remote_get($url, $args); if (is_wp_error($response)) { return false; } $code = wp_remote_retrieve_response_code($response); if ($code === 200) { $body = wp_remote_retrieve_body($response); $data = json_decode($body, true); if (! isset($data['values']) || empty($data['values'][0])) { return true; // Header is NOT added } // update_option('checked_header', $data); return false; } return true; } /** * Append a row to the sheet. * * @return object|WP_Error The API response or an error. */ public static function add_new_row($sheet_id, $sheet_name, $row) { if (empty($sheet_id) || empty($sheet_name)) { return new WP_Error('alphagf_gs_missing_sheet', 'No spreadsheet or sheet selected.'); } return self::make_request('sheets', 'POST', '/' . rawurlencode($sheet_id) . '/values/' . self::build_range($sheet_name, 'A1') . ':append?valueInputOption=USER_ENTERED', $row); } }