Recently, I published an article on How to Integrate Google Sheets API with PHP where I explained how to write data to the Google Sheets using PHP and Google APIs. One of the readers asked how to write data to the OneDrive Sheet programmatically. Like Google Drive, the Microsoft OneDrive service is used to store files and folders. 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 standard for authorization. That means we need to generate an access token using the OAuth flow. For this, you need to first register the application with Microsoft and grab your credentials.

Register an Application and Create Credentials

While integrating an OAuth flow, 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 register their application with Microsoft.
Log in 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 in the details as shown in the screenshot below.

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

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

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

Install Required Packages

Next, 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 install these 2 packages using a Composer. Create a composer.json file in your project root directory and add the below lines to it.

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

Run the command below which will install these packages.

composer install

The OAuth process requires you to generate an access token that 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 problem, we use the ‘refresh_token’ to regenerate the access token in the background. The newly generated access token is then used for API calls. This is how OAuth works.

Database Configuration

For real-world applications, we need to store the access token in a safe place. This stored token will be used while calling the APIs. To store the token details create a database table ‘onedrive_oauth’ as follows.

CREATE TABLE `onedrive_oauth` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `provider` varchar(255) NOT NULL,
 `provider_value` text NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

We will insert the token into the database after completing the authorization process. We will also require to update the token in the background on expiry. 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 onedrive_oauth WHERE provider = 'onedrive'");
        if($result->num_rows) {
            return false;
        }
 
        return true;
    }
 
    public function get_access_token() {
        $sql = $this->db->query("SELECT provider_value FROM onedrive_oauth WHERE provider = 'onedrive'");
        $result = $sql->fetch_assoc();
        return json_decode($result['provider_value']);
    }
 
    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 onedrive_oauth(provider, provider_value) VALUES('onedrive', '$token')");
        } else {
            $this->db->query("UPDATE onedrive_oauth SET provider_value = '$token' WHERE provider = 'onedrive'");
        }
    }
}

Generate Access Token for OneDrive API

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. Let’s follow them. Create a config.php file and add the below code to 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. 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();
    $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 ‘onedrive_oauth’ table.

Add Table in OneDrive Sheet

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

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

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

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

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

Keep a note when we insert the table for the 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 its value becomes ‘Table2’, ‘Table3’, 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.

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, and creating an Excel workbook with a table in it. Now, we are good to go ahead and write a code that 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 3 values. You can pass as many array elements. You also 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

• Speech-To-Text using Amazon Transcribe in PHP
• Send Email using Mailjet in PHP
• PayPal Payment Gateway Integration in PHP using PayPal REST API

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