# Retrieving data from a privately owned Google Spreadsheet via API without OAuth This is a sequel to my previous blog post on [Retrieving data from a public Google Spreadsheet and display it on a webpage](https://anjanesh.dev/retrieving-data-from-a-public-google-spreadsheet-and-display-it-on-a-webpage). Only, this time, we're using a server-side code (PHP) to fetch the data from the Google Spreadsheet since : not only do we not want the end-user to 'know' the Spreadsheet URL, we can't expose the Google Cloud API details in the browser to fetch. Goto : https://console.cloud.google.com/apis/ Create a project or select an existing project > APIs & Services > Enable APIs and Services > Google Sheets > Enable ### Create Credentials > Service Account ![create-credentials.webp](https://cdn.hashnode.com/res/hashnode/image/upload/v1660964892838/Gte9lm1xJ.webp align="left") ### Copy the service account email and **share the privately owned Google Spreadsheet with this email address** - this is the most important point in this article. ![service-account-details.webp](https://cdn.hashnode.com/res/hashnode/image/upload/v1660965042053/mvbTnntrG.webp align="left") ### Again, its the newly created Service Account's Email Address that needs to be copied and share *this* Email Address (circled in red) with the your private Google Sheet document : ![email.webp](https://cdn.hashnode.com/res/hashnode/image/upload/v1660967688903/nE3puekwc.webp align="left") ### Now, goto Keys and create a new key. Goto [https://console.cloud.google.com/iam-admin/serviceaccounts?walkthrough\_id=iam--create-service-account-keys&start\_index=1#step\_index=1](https://console.cloud.google.com/iam-admin/serviceaccounts?walkthrough_id=iam--create-service-account-keys&start_index=1#step_index=1) as mentioned [here](https://cloud.google.com/iam/docs/creating-managing-service-account-keys) : 1. Select a project. 2. Click the email address of the service account that you want to create a key for. 3. Click the **Keys** tab. 4. Click the **Add key** drop-down menu, then select **Create new key**. 5. Select **JSON** as the **Key type** and click **Create**. Clicking **Create** downloads a service account key file. After you download the key file, you cannot download it again. The downloaded key has the following format, where `PRIVATE_KEY` is the private portion of the public/private key pair: ```xml {   "type": "service_account",   "project_id": "PROJECT_ID",   "private_key_id": "KEY_ID",   "private_key": "-----BEGIN PRIVATE KEY-----\nPRIVATE_KEY\n-----END PRIVATE KEY-----\n",   "client_email": "SERVICE_ACCOUNT_EMAIL",   "client_id": "CLIENT_ID",   "auth_uri": "https://accounts.google.com/o/oauth2/auth",   "token_uri": "https://accounts.google.com/o/oauth2/token",   "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",   "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/SERVICE_ACCOUNT_EMAIL" } ``` ![shets-keys.webp](https://cdn.hashnode.com/res/hashnode/image/upload/v1660965958624/btv-FuwtE.webp align="left") ### Create Private Key and download the JSON file - save it as `credentials.json` - This JSON file cannot be recovered later on at any point of time, if lost. So, store this carefully. ![create-private-key.webp](https://cdn.hashnode.com/res/hashnode/image/upload/v1660965986509/3VNqsKht4.webp align="left") ```xml php composer.php require google/apiclient ``` `touch google-api-sheets.php` Edit google-api-sheets.php : (https://developers.google.com/sheets/api/quickstart/php) ```xml setApplicationName('Google Sheets API Service Account'); $client->setScopes('https://www.googleapis.com/auth/spreadsheets'); $client->setAuthConfig('credentials.json'); // This the location of your JSON file downloaded from Google Cloud's Service Account Credentials $service = new Google\Service\Sheets($client); // https://docs.google.com/spreadsheets/d/1zwblTR5DWzgOiVxsDYJJkP5Gcmi4OGiCtldI0nyxZGo/edit try { $spreadsheetId = '1zwblTR5DWzgOiVxsDYJJkP5Gcmi4OGiCtldI0nyxZGo'; // This is the sheet name with cell-values range for example $range = 'content-pages!A1:B15'; $range = 'Sheet1'; $response = $service->spreadsheets_values->get($spreadsheetId, $range); $values = $response->getValues(); if (empty($values)) { print "No data found.\n"; } else { print_r($values); } } catch(Exception $e) { // TODO(developer) - handle error appropriately echo 'Message: ' .$e->getMessage(); } ?> ``` `php google-api-sheets.php` ```xml Array ( [0] => Array ( [0] => Title [1] => Description ) [1] => Array ( [0] => What's the difference between UI Kits and libraries [1] => UI Kits are copy-and-pastable components which can be accessed through our website. Purchasing a UI Kit will not grant access to it's counter library like Blade UI. Libraries are ready to use components for frameworks, these can be locally downloaded through a package manager like Composer. Purchasing a library will not grant access to it's counter UI Kit and libraries for other frameworks. ) [2] => Array ( [0] => Can I use this with my team? [1] => Yes! you can use this package with your team as long as you purchase it specifically for the team. We recommend using a team email address. ) [3] => Array ( [0] => Can I redistribute the components? [1] => No, you can't redistribute our component in any shape or form. It's also not allowed to build other UI kits based on Razor UI. ) [4] => Array ( [0] => Can I use the components for open-source projects? [1] => Yes, as long as the main purpose of the project isn't something like a page builder or an admin panel package. It's completely okay to use our components as a part of a bigger and unrelated open-source project. ) [5] => Array ( [0] => Can I make a (paid) tool with the components? [1] => Same as the open-source projects. You can use Razor UI to build a paid tool as long as the tool isn't a page builder or an admin panel builder. ) [6] => Array ( [0] => Can I use this for client work? [1] => Same as the open-source projects. You can use Razor UI for client work, as long as the client project isn't a UI kit or a tool for building a page builder or an admin panel builder. ) [7] => Array ( [0] => Is there a project limit? [1] => No. You can use Razor UI for as many projects as you like. ) [8] => Array ( [0] => Will I get upcoming UI Kits and libraries for free? [1] => No, they're considered different products. You'll need to purchase a new license. However, customers get an exclusive discount on new releases. ) [9] => Array ( [0] => How much do updates cost? [1] => Updates for each package are forever free, at no additional cost. ) [10] => Array ( [0] => Do I get discounts on new packages? [1] => Yes. We appreciate our customers, so we reward them with discounts on any Razor UI packages we release in the future. ) [11] => Array ( [0] => Are there Vue.js and React.js examples? [1] => No. The templates use Alpine.js because it's part of the template, and easy to port to other frameworks. You'll find the code intuitive regardless of your coding background. ) [12] => Array ( [0] => Can I provide suggestions? [1] => We appreciate them! As part of your purchase, you'll get access to our Discord server where you can provide suggestions and we'll gladly discuss them. ) [13] => Array ( [0] => Is there support? [1] => We only offer support for payment-related issues. However, in the Discord community - that's part of your purchase - you can talk to us and other customers. The odds are likely someone will be able to help you out. ) [14] => Array ( [0] => What are libraries? [1] => Libraries are implementations with a complete API for a specific framework. Instead of HTML, you get ready to use building blocks. You can see their API before you purchase them. ) ) ``` PS: The Google Spreadsheet mentioned here is a publicly shared one and not a private one because I used this same spreadsheetId is the previous blog post. You can update the `$spreadsheetId` with your private Google Spreadsheet ID in the code above. It should still work.