How To Read and Update Data From Google Sheets Using PHP

Create New Project on the Google Cloud Platform The first step to creating a new project to be able to access Google’s resources is to go to the Google Cloud Console https://console.cloud.google.com/ and New Project as below: Fill in the basic details that are asked on the form you get when you click New Project and submit it and you should be done.

After create navigtate to project, like - https://console.cloud.google.com/welcome?project=project-spreadsheet-xxx In our case, since we plan to use the Google Sheets API, we will have to first enable the said API. To do that, check below screenshot click Enabled APIs & Services and then, from the top, click ENABLE APIS AND SERVICES. Or directly go to https://console.cloud.google.com/apis/library?project=project-spreadsheet-xxx to enable APIs you need. Find Google Sheets API and enable like below:

Once enabled, we now have to create a credentials. For our case, we want to create a Service Account. Here, make sure you choose Owner as your role and then finally move ahead to create the service account.

After the successful creation of the service account, you will be redirected to the list of credentials. On that page, by clicking the Edit icons as shown below, you will be redirected to another page from where we can download the credentials as a JSON file. Just follow along. Also, don’t forget to copy the email id that should be listed below the Service Accounts section. Use this link https://console.cloud.google.com/iam-admin/settings to delete any existing project. Use this link https://console.cloud.google.com/cloud-resource-manager for all of your projects.

Install the Google API Client Package In order to interact with a google sheet using Composer, we will need to first bring in the Google API Client package. Let’s do that by running the following command: composer require google/apiclient

ont forget to copy the email id that should be listed below the Service Accounts section. We will need this email id. So, go to your Google Cloud Console and from under Service Accounts, copy this email id.

Now, let’s create a new Google sheet by going to docs.google.com and create a new spreadsheet. Then click the Share button on the top right of the newly created Google sheet and paste your service account email id here, to share the sheet with this email id (which is essentially your service account). like below

Below is full PHP script to get Google Sheet data and update accordingly:

<?php use Google\Client; use Google\Service\Drive; use Google\Service\Sheets; use Google\Service\Sheets\ValueRange; set_time_limit(60 * 60 * 12); ini_set("memory_limit", "-1"); ini_set('display_errors', 1); error_reporting(E_ALL); ini_set('display_startup_errors', 1); $client = new Client(); $client->setAuthConfig('creds.json'); $client->setScopes(['https://www.googleapis.com/auth/spreadsheets']); $client->useApplicationDefaultCredentials(); $service = new Sheets($client); $spreadsheetId = "1AN34n84blDoH_xxxxxxxxxx_faP6S5pjO5xaGdk7t77qPAgQY"; $ranges = 'Sheet1!A1:C20'; $params = array( 'ranges' => $ranges, 'majorDimension' => 'ROWS' ); //execute the request $result = $service->spreadsheets_values->batchGet($spreadsheetId, $params); if (!$result) { return [ 'error' => true, 'message' => 'missing row data' ]; } $values = $result->getValueRanges(); echo "<pre>"; print_r($values); echo "</pre>"; $updateRow = []; $updateRow[] = ["Name1", "Id1", "Group1-".time()]; $valueRange = new ValueRange(); $valueRange->setValues($updateRow); $range = 'Sheet1!A2:C'.(count($updateRow) + 1); // where the replacement will start, here, first column and second line $options = ['valueInputOption' => 'USER_ENTERED']; $service->spreadsheets_values->update($spreadsheetId, $range, $valueRange, $options);

Respective Google Sheet is as below after update:

Using JavaScript to Upload Large Files in Chunks as Parts and Avoid Server Limits

We know that PHP config files has the limit to upload files to server, you know that uploading large files can be a real pain. You have to find the loaded php.ini file, edit the upload_max_filesize and post_max_size settings, and hope that you never have to change servers and do all of this over again.

We will do some trick to upload very very large file to server without manipulate any configuration, does not matter how small the limit is. We actually will send files from browser chunk by chunk. Chunk can be as small as our demand. Suppose if our server accept max 1mb request limit, we will send 1mb per request and many more.

This will include two parts (1) is to send files chunk by chunk from browser to server and the other part (2) is save file parts into single file. I will use PHP example at backend to save file into server.

So below is Javascript parts with description:

<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>File Upload Chunk</title> </head> <body> <div class="form"> <input type="file" id="file"/> <button type="button" id="upload">Upload</button> </div> <div class="toast"> <div id="toast"></div> </div> <script type="text/javascript"> document.getElementById("upload").addEventListener("click", () => { let files = document.getElementById("file").files; if (files.length === 0) { alert("File required"); } else { showToast("Uploading"); doUploadFileChunk(files[0], 1); } }); function doUploadFileChunk(file, chunk) { let chunkLimit = 1024 * 50; // Limit of file chunk send to server is 50 KB for test purpose // Blob from position let fromSlice = (chunk - 1) * chunkLimit; // Blob to position let nextSlice = fromSlice + chunkLimit - 1; // new FormData() let formData = new FormData(); formData.append("file_number", chunk); formData.append("file_name", file.name); formData.append("file_part", file.slice(fromSlice, nextSlice + 1)); // Processing small part of file console.log(`Sending from ${fromSlice} to ${nextSlice} KB`); // Sending to server let xhr = new XMLHttpRequest(); xhr.open("POST", "upload.php", true); xhr.setRequestHeader("Accept", "application/json"); xhr.onreadystatechange = () => { if (xhr.readyState === 4) { console.log("Status = " + xhr.status); try { let response = JSON.parse(xhr.responseText); console.log(response); if (response.uploaded) { let percent = Math.floor((nextSlice / file.size) * 100); if (nextSlice < file.size) { showToast(`Uploading file - ${percent}% completed`); setTimeout(() => { doUploadFileChunk(file, chunk + 1); }, 200); } else { showToast(`Upload completed, processing started`); } } else { alert("Failed to upload file"); } } catch (e) { console.log("Complete = " + xhr.responseText); console.log(e); alert("Failed to upload file"); } } }; xhr.send(formData); } function showToast(text) { document.getElementById("toast").innerHTML = text; } </script> <style type="text/css"> div.form { padding: 20px; } div.toast { padding: 20px; background-color: green; color: white; font-size: 31px; } </style> </body> </html>

And respective PHP file is as below:

<?php $output = ["uploaded" => true, "file" => $_FILES["file_part"]]; $uploadFile = sprintf("storage/%s", $_POST['file_name']); if ($_POST["file_number"] == 1) { if (file_exists($uploadFile)) { unlink($uploadFile); } } $destination = fopen($uploadFile, "a+"); if (FALSE === $destination) die("Failed to open destination"); $handle = fopen($_FILES["file_part"]["tmp_name"], "rb"); if (FALSE === $handle) die("Failed to open blob"); $BUFFER_SIZE=1*1024*1024; // 1MB, bigger is faster while( !feof($handle) ) { fwrite($destination, fread($handle, $BUFFER_SIZE) ); } fclose($handle); fclose($destination); echo json_encode($output);

Streaming HTTP response in PHP - turn long-running process into realtime UI

Streaming is not a new concept, it is a data transfer technique which allows a web server to continuously send data to a client over a single HTTP connection that remains open indefinitely. In streaming response comes in chunk rather than sending them at once. In the traditional HTTP request / response cycle, a response is not transferred to the browser until it is fully prepared which makes users wait.

Output buffering allows to have output of PHP stored into an memory (i.e. buffer) instead of immediately transmitted, it is a mechanism in which instead of sending a response immediately we buffer it in memory so that we can send it at once when whole content is ready.

Each time using echo we are basically telling PHP to send a response to the browser, but since PHP has output buffering enabled by default that content gets buffered and not sent to the client. But we will tell PHP to send output immediately then appear rather keep them wait until execution completed.

Php script will be like below which will usually send content chunk by chunk:

<?php // Making maximum execution time unlimited set_time_limit(0); // Send content immediately to the browser on every statement that produces output ob_implicit_flush(1); // Deletes the topmost output buffer and outputs all of its contents ob_end_flush(); sleep(1); echo sprintf("data: %s%s", json_encode(["content" => "Stream 1"]), "\n\n"); sleep(2); echo sprintf("data: %s%s", json_encode(["content" => "Stream 2"]), "\n\n"); sleep(3); echo sprintf("data: %s%s", json_encode(["content" => "Stream 3"]), "\n\n"); exit;

Output buffers catch output given by the program. Each new output buffer is placed on the top of a stack of output buffers, and any output it provides will be caught by the buffer below it. The output control functions handle only the topmost buffer, so the topmost buffer must be removed in order to control the buffers below it. ✔ The ob_implicit_flush(1) enables implicit flushing which sends output directly to the browser as soon as it is produced. ✔ If you need more fine grained control then use flush() function. To send data even when buffers are not full and PHP code execution is not finished we can use ob_flush and flush. The flush() function requests the server to send it's currently buffered output to the browser How to get and process the response in javascript There is a simple example how we can do it with traditional xhr ( XMLHTTPRequest ) request

function doCallXHR() { let lastResponseLength = 0; let xhr = new XMLHttpRequest(); xhr.open("POST", "/do", true); xhr.setRequestHeader("Content-Type", "application/json"); xhr.setRequestHeader("Accept", "application/json"); xhr.onprogress = (e:any) => { let response = e.currentTarget.response; let progressResponse = lastResponseLength > 0 ? response.substring(lastResponseLength) : response; lastResponseLength = response.length; console.log(new Date().toUTCString()); progressResponse.split(/\n\n/g).filter((t) => t.trim().length > 0).forEach((line) => { console.log(JSON.parse(line.substring(6))); }); }; xhr.onreadystatechange = () => { if (xhr.readyState == 4) { console.log("Status = " + xhr.status); console.log("Complete = " + xhr.responseText); } }; xhr.send(); } doCallXHR();

Output is as below from browser console: Wed, 10 Jul 2024 13:33:36 GMT {content: 'Stream 1'} Wed, 10 Jul 2024 13:33:38 GMT {content: 'Stream 2'} Wed, 10 Jul 2024 13:33:41 GMT {content: 'Stream 3'} Status=200 Complete=data: {"content":"Stream 1"} data: {"content":"Stream 2"} data: {"content":"Stream 3"}

xhr.onprogress: is the function called periodically with information until the XMLHttpRequest completely finishes Few points to note​ ✅ we are sending chunk response from server and in xhr onprogress getting every new response part merged with the previously received part. ✅ it is possible to load the response one at a time as server response is multiple parts & in a format one after another. We can do it by substracting previoud response string length and parsing with JSON.parse What if any error / exception occur! how to react to that?​ That's easy.. catch the error & respond with a status that the front-end js script can react to

<?php try { $response = $this->expensiveProcessing(); } catch(\Exception $e) { // Handle the exception echo json_encode([ 'success' => false, 'message' => $e->getCode() . ' - '. $e->getMessage(), 'progress' => 100 ]); ob_end_flush(); exit; }

Configuration for Nginx​ You need to do few tweaking with nginx server before working with output buffering. fastcgi_buffering off; proxy_buffering off; gzip off; For whichever reason if you don't have access to nginx server configuration then from PHP code you can also achieve the same result via HTTP header header('X-Accel-Buffering: no');

PHP image output and browser caching

This post is for return image from server by PHP script and cache in browser to save on our bandwidth.

Below code will return image using PHP script <?php function image_function_cache($file_name) { $file = "WWW_ROOT/public/img/$file_name"; if (!file_exists($file) || !is_file($file)) { echo ""; exit; } $fileTime = filemtime($file); $headerTime = IfModifiedSince(); // Will return 304 when image source not changed if (!is_null($headerTime) && (strtotime($headerTime) == $fileTime)) { header('Last-Modified: '.gmdate('D, d M Y H:i:s', $fileTime).' GMT', true, 304); exit; } session_cache_limiter('none'); $type = 'image/png'; header('Content-Type:'.$type); header('Content-Length: ' . filesize($file)); header('Cache-control: max-age='.(60*60*24*365)); header('Expires: '.gmdate(DATE_RFC1123,time()+60*60*24*365)); header('Last-Modified: '.gmdate('D, d M Y H:i:s', $fileTime).' GMT', true, 200); readfile($file); exit; } function IfModifiedSince() { if (function_exists("apache_request_headers")) { if ($headers = apache_request_headers()) { if (isset($headers['If-Modified-Since'])) { return $headers['If-Modified-Since']; } } } if (isset($_SERVER['HTTP_IF_MODIFIED_SINCE'])) { return $_SERVER['HTTP_IF_MODIFIED_SINCE']; } return null; }

Check below image, there are two request for an image, first request status is 200 and image returned, but 2nd call if you see Size column image returned from cache. All happened for above functionality:

How to Send files via POST with cURL and PHP (Example)

In this process cURL send file to any web server along with other params. Check it out below code:

Assuming that you're using PHP 5.5+, you need to use CURLFile for uploading your file:

<?php $headers = array( 'Authorization: Bearer Token Value', 'Content-type: multipart/form-data' ); $url = "https://example.com/api/v1/import/uploadfile"; $post_data = array( "file1" => new CURLFile("/var/www/files/file1.csv", 'text/csv', 'File1.csv'), "file2" => new CURLFile("/var/www/files/file1.pdf", 'application.pdf', 'File1.pdf'), "param1" => "Test" ); $curl = curl_init(); curl_setopt($curl, CURLOPT_RETURNTRANSFER, true); curl_setopt($curl, CURLOPT_VERBOSE, true); curl_setopt($curl, CURLOPT_HEADER, false); curl_setopt($curl, CURLOPT_POST, true); curl_setopt($curl, CURLOPT_URL, $url); curl_setopt($curl, CURLOPT_POSTFIELDS, $post_data); curl_setopt($curl, CURLOPT_HTTPHEADER, $headers); $response = curl_exec($curl); $status = curl_getinfo($curl, CURLINFO_HTTP_CODE); curl_close($curl);

Create CSV file containing UTF-8 characters in PHP and Javascript | Illegal Character \ufeff Problem

If you create or read a CSV file and the file contains some characters such as ü or ş, then you will find the characters are not displayed correctly in Excel. This is because the default coding of Excel is not UTF-8. To force Excel to use UTF-8, we need to add BOM (Byte Order Mark) in the beginning of the file.

<?php // When reading csv file using PHP // BOM as a string for comparison. $bom = "\xef\xbb\xbf"; // Read file from beginning. $fp = fopen($path, 'r'); // Progress file pointer and get first 3 characters to compare to the BOM string. if (fgets($fp, 4) !== $bom) { // BOM not found - rewind pointer to start of file. rewind($fp); } // Read CSV into an array. $lines = array(); while(!feof($fp) && ($line = fgetcsv($fp)) !== false) { $lines[] = $line; }

// When writting to csv file $fp = fopen($myFile, 'w'); fputs($fp, $bom =( chr(0xEF) . chr(0xBB) . chr(0xBF) )); fputcsv($fp, $otherdata);

// Javascript var csvFormattedDataTable = ''; csvFormattedDataTable += "\uFEFF"; csvFormattedDataTable += "other stuff"; var encodedUri = 'data:application/csv;charset=utf-8,' + encodeURIComponent(csvFormattedDataTable); $(buttonName).attr("href", encodedUri); $(buttonName).attr("download", 'table-data.csv'); $(buttonName).attr("target", '_blank');

Composer require runs out of memory. PHP Fatal error: Allowed memory size of 1610612736 bytes exhausted

To get the current memory_limit value, run: php -r "echo ini_get('memory_limit').PHP_EOL;" Try increasing the limit in your php.ini file (ex. C:\xampp\php\php.ini for Windows-like systems): ; Use -1 for unlimited or define an explicit value like 2G memory_limit = -1 Or, you can increase the limit with a command-line argument: php -d memory_limit=-1 composer.phar require hwi/oauth-bundle php-http/guzzle6-adapter php-http/httplug-bundle

PHP & MySQL PDO: PDO binding values for MySQL IN statement - Bind an array to an IN() condition?

The answer there was that, for a variable sized list in the in clause, you'll need to construct the query yourself.

Consider your array like:

$values = array(1, 2, 3, 4, 5); $count = count($values); $criteria = sprintf("?%s", str_repeat(",?", ($count ? $count - 1 : 0))); $sql = sprintf("DELETE FROM table where column NOT IN(%s)", $criteria); $pdo->sth = prepare($sql); $pdo->sth->execute($values); SQL should be: DELETE FROM table where column IN (?,?,?,?,?)