Automating Google Sheets Downloads to Excel: A Simple PHP Script

In the digital age, managing data efficiently is crucial. Google Sheets, a widely used tool for collaborative data management, sometimes needs to be converted into different formats for further processing. One common requirement is to download a publicly shared Google Sheet as an XLSX file, which can then be used in Excel for advanced data analysis. Here’s a handy PHP script that automates this process.

Why Automate Google Sheets Downloads?

Downloading Google Sheets manually can be tedious, especially if the data is updated frequently. Automating the download process ensures that you always have the latest data in your desired format without repetitive manual effort. Additionally, the XLSX format is compatible with Excel’s powerful features like Power Query, making it easier to transform and analyze data.

The PHP Script and Explanation

function download_google_sheet($sheet_url, $output_file) {
    // Initialize a cURL session
    $ch = curl_init();

    // Set the URL of the Google Sheet
    curl_setopt($ch, CURLOPT_URL, $sheet_url);

    // Return the transfer as a string
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);

    // Follow redirects
    curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);

    // Set timeout
    curl_setopt($ch, CURLOPT_TIMEOUT, 30);

    // Execute the cURL session
    $response = curl_exec($ch);

    // Check for cURL errors
    if (curl_errno($ch)) {
        echo 'Error:' . curl_error($ch);
        curl_close($ch);
        return false;
    }

    // Check HTTP response code
    $http_code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    if ($http_code != 200) {
        echo 'Error: HTTP Status Code ' . $http_code;
        curl_close($ch);
        return false;
    }

    // Close the cURL session
    curl_close($ch);

    // Save the response to the output file in binary mode
    $file = fopen($output_file, 'wb');
    if ($file) {
        fwrite($file, $response);
        fclose($file);
        return true;
    } else {
        echo 'Error: Unable to open file for writing.';
        return false;
    }
}

// Google Sheet URL
$sheet_url = "https://docs.google.com/spreadsheets/d/1di8rOr-FeZbXP5AmjsL14Jwn6-XGYi9QyhifFCrwJDE/export?format=xlsx";

// Output file name
$output_file = "download-file.xlsx";

// Download the Google Sheet
if (download_google_sheet($sheet_url, $output_file)) {
    echo "File downloaded successfully and saved as $output_file";
} else {
    echo "Failed to download file.";
}

This PHP script automates the download of a Google Sheet in XLSX format and saves it locally. Here’s a step-by-step breakdown of how the script works:

1. Function Definition

The script starts by defining a function download_google_sheet that takes two parameters: the URL of the Google Sheet and the desired output file name.

function download_google_sheet($sheet_url, $output_file) {

2. Initializing cURL Session

A cURL session is initialized to handle the HTTP request.

$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $sheet_url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);
curl_setopt($ch, CURLOPT_TIMEOUT, 30);

3. Executing the Request

The script executes the cURL session and captures the response.

$response = curl_exec($ch);

4. Error Handling

It checks for any cURL errors and the HTTP response code to ensure the request was successful.

if (curl_errno($ch)) {
    echo 'Error:' . curl_error($ch);
    curl_close($ch);
    return false;
}
$http_code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
if ($http_code != 200) {
    echo 'Error: HTTP Status Code ' . $http_code;
    curl_close($ch);
    return false;
}
curl_close($ch);

5. Saving the File

If the request is successful, the script saves the response as an XLSX file in binary mode.

$file = fopen($output_file, 'wb');
if ($file) {
    fwrite($file, $response);
    fclose($file);
    return true;
} else {
    echo 'Error: Unable to open file for writing.';
    return false;
}

6. Script Execution

Finally, the script specifies the Google Sheet URL and the output file name, then calls the function to download the sheet.

$sheet_url = "https://docs.google.com/spreadsheets/d/1di8rOr-FeZbXP5AmjsL14Jwn6-XGYi9QyhifFCrwJDE/export?format=xlsx";
$output_file = "download-file.xlsx";

if (download_google_sheet($sheet_url, $output_file)) {
    echo "File downloaded successfully and saved as $output_file";
} else {
    echo "Failed to download file.";
}

Benefits of the Resulting XLSX File

The resulting XLSX file is saved in the same directory as the PHP script, making it easily accessible. This file can be seamlessly integrated with Excel’s Power Query, enabling users to perform complex data transformations and analyses. Power Query’s intuitive interface and powerful features make it an excellent tool for cleaning, reshaping, and analyzing data, enhancing your productivity and insights.

By using this script, you can automate the tedious process of downloading Google Sheets and ensure you always have the latest data ready for advanced analysis in Excel.