Write Data to OneDrive Sheet using Microsoft Graph API and PHP

Recently, I have published an article How to Integrate Google Sheets API with PHP where I explained how to write data to the Google Sheet using PHP and Google APIs. One of the readers asked in a similar way how to write data to the OneDrive Sheet. In this tutorial, we will study how to use Microsoft Graph API and PHP to write the data in the OneDrive Sheet.

Microsoft Graph API follows the OAuth flow for the authorization. That means, we need to generate an access token using the OAuth process. For this, you need to first register the application and grab your credentials.

Register an Application and Create Credentials

While integrating an OAuth process, you need to have a client id and client secret. You will get it after registering the applications. One can follow the steps mentioned on this page and registers their application with Microsoft.

Login to the Azure Portal and then search for App Registration. Fill up the form. You need to pass an URL to the Redirect URI field. To this field, I am passing a local server URL. I’ll create a callback.php in the next steps. I have filled up the details as shown in the screenshot below.

register-application

Upon submitting a form, you will get the Client ID from the Overview tab.

client-id

Next, go to the ‘Certificates and secrets’ tab and click on the ‘New client secret’.

create-client-secret

It will open a pop up where you need to add a description and choose the Expires option.

client-secret-info

After this, you will get your Client secret as shown below.

client-secret

Install Required Packages

We have to integrate two things – OAuth and REST API. For the OAuth, I am going to use the Hybridauth library. And to interact with the Microsoft Graph API let’s use the Guzzle library.

We can easily install these 2 packages using a Composer. Create a composer.json file in your project root directory and add below lines to it.

{
    "require" : {
        "hybridauth/hybridauth" : "~3.0",
        "guzzlehttp/guzzle": "^7.0"
    }
}

Run the command below which will install these packages.

composer install

As said, Microsoft Graph API uses OAuth to perform the API operations. It requires you to generate an access token which acts as an identifier of your account. Using this access token, Microsoft Graph APIs validate whether the incoming request is valid and authorized.

The access token has a short life. They expire soon. And once expired, we can’t do API calls as Microsoft treats the request with the expired token as an unauthorized request. To resolve this issue, we use the ‘refresh_token’ to regenerate the access token in the background. This is how OAuth works.

Database Configuration

To achieve our goal, we need to store the token in a safe place. We need the token while calling the APIs. Also, it should regenerate after it’s expiry. So to store the token details create a database table as follows.

CREATE TABLE `token` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `access_token` text NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

We will insert the token in the database after completing the authorization process. Also, we will update the token in the background. For this, I am going to create a database class so we can easily do the insert, select, and update operations.

class-db.php

<?php
class DB {
    private $dbHost     = "DB_HOST";
    private $dbUsername = "DB_USERNAME";
    private $dbPassword = "DB_PASSWORD";
    private $dbName     = "DB_NAME";
     
    public function __construct(){
        if(!isset($this->db)){
            // Connect to the database
            $conn = new mysqli($this->dbHost, $this->dbUsername, $this->dbPassword, $this->dbName);
            if($conn->connect_error){
                die("Failed to connect with MySQL: " . $conn->connect_error);
            }else{
                $this->db = $conn;
            }
        }
    }
 
    public function is_table_empty() {
        $result = $this->db->query("SELECT id FROM token");
        if($result->num_rows) {
            return false;
        }
 
        return true;
    }
 
    public function get_access_token() {
        $sql = $this->db->query("SELECT access_token FROM token");
        $result = $sql->fetch_assoc();
        return json_decode($result['access_token']);
    }
 
    public function get_refersh_token() {
        $result = $this->get_access_token();
        return $result->refresh_token;
    }
 
    public function update_access_token($token) {
        if($this->is_table_empty()) {
            $this->db->query("INSERT INTO token(access_token) VALUES('$token')");
        } else {
            $this->db->query("UPDATE token SET access_token = '$token' WHERE id = (SELECT id FROM token)");
        }
    }
}

Generate Access Token

Once you are ready with the required packages and database class, the next part is doing the authentication and storing the token details in the database.

Hybridauth library provides its own process to perform authorization. So, let’s follow them. Create a config.php file and add the below code in it.

config.php

<?php
require_once 'vendor/autoload.php';
require_once 'class-db.php';

define('ONEDRIVE_CLIENT_ID', 'CLIENT_ID_HERE');
define('ONEDRIVE_CLIENT_SECRET', 'CLIENT_SECRET_HERE');
define('ONEDRIVE_SCOPE', 'files.read files.read.all files.readwrite files.readwrite.all offline_access');
define('ONEDRIVE_CALLBACK_URL', 'CALLBACK_URL_HERE'); // in my case it is http://localhost/sajid/onedrive/callback.php

$config = [
    'callback' => ONEDRIVE_CALLBACK_URL,
    'keys'     => [
                    'id' => ONEDRIVE_CLIENT_ID,
                    'secret' => ONEDRIVE_CLIENT_SECRET
                ],
    'scope'    => ONEDRIVE_SCOPE,
    'authorize_url_parameters' => [
            'approval_prompt' => 'force',
            'access_type' => 'offline'
    ]
];

$adapter = new Hybridauth\Provider\MicrosoftGraph( $config );

Make sure to replace all placeholders with their actual values. I have passed the required scopes for our end goal so keep it as it is. Now, in the callback.php which is my callback URL, I’ll write a code that starts the authentication process, redirects to it after successful authorization, and stores the tokens information in the database table.

callback.php

<?php
require_once 'config.php';

try {
    $adapter->authenticate();
    $token = $adapter->getAccessToken();
    $db = new DB();
    if($db->is_table_empty()) {
        $db->update_access_token(json_encode($token));
        echo "Access token inserted successfully.";
    }
}
catch( Exception $e ){
    echo $e->getMessage() ;
}

Run the callback.php on the browser, complete the authentication process. Upon completion of authentication, you should see the token details stored inside the ‘tokens’ table.

Add Table in OneDrive Sheet

To add the data through Microsoft Graph API, you need to insert a table in the OneDrive sheet. Head over to the OneDrive page. Click on the ‘Excel workbook’ under the ‘New’ dropdown.

create-sheet

Once you are on the sheet page, click on the ‘Table’ icon and then ‘OK’ from the opened pop-up.

insert-table

After this, you will see the Table inserted into your OneDrive sheet as follows.

sheet-table

Adjust the columns as per your requirements. In my case, I kept 3 columns.

Now, one can ask why need of inserting a table in the sheet? When I was researching this topic, I found this is the only way of writing data to the Excel workbook. You may read more about this on their documentation.

Keep a note when we insert the table first time in the sheet its value is ‘Table1’. We need this value at the time of calling APIs. If for some reason, you remove the table and insert it again then it’s value becomes ‘Table2’ and so on.

Copy your sheet ID which is required in an API call. You can get it from the browser URL as shown in the screenshot below.

sheet-id

Write Data to OneDrive Sheet with Microsoft Graph API and PHP

So far, we are done with installing the required packages, inserting tokens in the database, creating an Excel workbook with a table in it. Now, we are good to go ahead and write a code which appends data to the OneDrive sheet using the Microsoft Graph API and PHP.

I am creating a file append-to-onedrive-sheet.php and my code will be as written below.

<?php
require_once 'config.php';

// here you can pass as many data as a separate array element
$arr_data = [
    ['John Doe', 'john@test.com', '8888888888']
];

append_to_sheet($arr_data);

function append_to_sheet($arr_data = array()) {
    $item_id = 'YOUR_SHEET_ID';
    $table = 'Table1'; //in your case it can be Table2, Table3, ...

    $db = new DB();

    $arr_token = (array) $db->get_access_token();
    $accessToken = $arr_token['access_token'];

    try {

        $client = new GuzzleHttp\Client([
            // Base URI is used with relative requests
            'base_uri' => 'https://graph.microsoft.com',
        ]);

        $response = $client->request("POST", "/v1.0/me/drive/items/$item_id/workbook/tables/$table/rows/add", [
            'json' => [
                'values' => $arr_data
            ],
            'headers' => [
                'Authorization' => 'Bearer '. $accessToken
            ],
            'verify' => false,
        ]);
    } catch(Exception $e) {
        if( 401 == $e->getCode() ) {
            $refresh_token = $db->get_refersh_token();

            $client = new GuzzleHttp\Client(['base_uri' => 'https://login.microsoftonline.com']);

            $response = $client->request('POST', '/common/oauth2/v2.0/token', [
                'form_params' => [
                    "grant_type" => "refresh_token",
                    "refresh_token" => $refresh_token,
                    "client_id" => ONEDRIVE_CLIENT_ID,
                    "client_secret" => ONEDRIVE_CLIENT_SECRET,
                    "scope" => ONEDRIVE_SCOPE,
                    "redirect_uri" => ONEDRIVE_CALLBACK_URL,
                ],
            ]);

            $db->update_access_token($response->getBody());

            append_to_sheet($arr_data);
        } else {
            echo $e->getMessage(); //print the error just in case your video is not uploaded.
        }
    }
}

In the above code, I created an array that has the array element with 3 values. You can pass as many array elements. You have to pass your sheet id to the $item_id variable.

This code fetches an access token from the database and sends a POST request to the required API endpoint. If the token expires, it automatically regenerates it in the background and continues the process.

Try it and you should see your data is appended to the Excel workbook of your OneDrive account.

I hope you got to know how to write data to the OneDrive Sheet using Microsoft Graph API and PHP. Please share your thoughts and suggestions in the comment section below.

Related Articles

If you liked this article, then please subscribe to our YouTube Channel for video tutorials.

Leave a Reply

Your email address will not be published. Required fields are marked *