Question 375507 in PHP Question posted in
Official manual can be found here.

php file_put_contents to get data from Google Sheet and save as CSV files on WordPress Website

I have ZERO php experience, so my code/problem is purely based off Googling. I apologize in advance if you all cringe at my code.

I have created a php script which I run via cron every 5min to get data from a shared public, Google Sheet and save it as CSV files on my website’s server (Shared Hosting, WordPress)

The script runs fine but once in a blue moon I get a couple of errors… EG: It could run for 1 hour, nothing but the next hour I get the odd error all are "failed to open stream" related:

  1. HTTP request failed! HTTP/1.0 500 Internal Server Error

  2. HTTP request failed! HTTP/1.0 400 Bad Request

  3. HTTP request failed! [ No code ]

I assume 2 and 3 is purely just a communication error with my server connecting to the Google Sheet?

I assume 1 is just an error by my server.

I’ve added some comments in my code of what I am trying to do with each step…

It works fine, I am happy with it. I just wonder if I can eliminate the errors and / or (2nd part of my question) if anyone has some comments (please be brutal) on how to improve the code… I’m sure I can use loops instead of repeating 1600 lines of code.

Also, when I check my server resources, the script is having next to no impact so plenty of server resources available.

<?php

/// Global variables
$fileid = "2PACX-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"; // Google Sheet ID from File--> Share--> Publish to Web.
$save_location_Data = "/home/[ my-site ]/wp-content/uploads/database"; // where I am saving the CSV files on my server

/**
 * MEN.
 */
$division = "MEN";
$sheet_[$division] = "673108837"; // Google Sheet, Tab ID

$datatype = "Teams";
$range_[$division][$datatype] = "range=A1:B200"; // Selected range from tab
$url_[$division][$datatype] = "https://docs.google.com/spreadsheets/d/e/".$fileid."/pub?gid=".$sheet_[$division]."&".$range_[$division][$datatype]."&single=true&output=csv";
$filename_[$division][$datatype] = ($save_location_Data."/".$division."-".$datatype."_temp_.csv"); // I am just building the full URL here.
file_put_contents($filename_[$division][$datatype], file_get_contents($url_[$division][$datatype]));
clearstatcache();
if(filesize($filename_[$division][$datatype]) != 0) {
    rename($filename_[$division][$datatype], $save_location_Data."/".$division."-".$datatype.".csv");
    }
    else {
        unlink($filename_[$division][$datatype]);
}
// Just checking if the data is 0 bytes, if no, rename temp file to final file. If yes, delete. 
// I thought saving a temp file first would be quicker (more reliable) rather than file_put_contents to final file. 
// If the CSV data is requested by my website (in a table), it's only milliseconds the CSV file is locked to write new data so less chance of being "unavailable".

$datatype = "Stats";
$range_[$division][$datatype] = "range=C1:H200";
$url_[$division][$datatype] = "https://docs.google.com/spreadsheets/d/e/".$fileid."/pub?gid=".$sheet_[$division]."&".$range_[$division][$datatype]."&single=true&output=csv";
$filename_[$division][$datatype] =."/".$division."-".$datatype."_temp_.csv");
file_put_contents($filename_[$division][$datatype], file_get_contents($url_[$division][$datatype]));
clearstatcache();
if(filesize($filename_[$division][$datatype]) != 0) {
    rename($filename_[$division][$datatype]."/".$division."-".$datatype.".csv"); 
    }
    else {
        unlink($filename_[$division][$datatype]);
}
$datatype = "Games_1";
$range_[$division][$datatype] = "range=I1:J200";
$url_[$division][$datatype] = "https://docs.google.com/spreadsheets/d/e/".$fileid."/pub?gid=".$sheet_[$division]."&".$range_[$division][$datatype]."&single=true&output=csv";
$filename_[$division][$datatype] =."/".$division."-".$datatype."_temp_.csv");
file_put_contents($filename_[$division][$datatype], file_get_contents($url_[$division][$datatype]));
clearstatcache();
if(filesize($filename_[$division][$datatype]) != 0) {
    rename($filename_[$division][$datatype]."/".$division."-".$datatype.".csv");
    }
    else {
        unlink($filename_[$division][$datatype]);
}

// The above code repeats about 100+ times to create 120x CSV files.
// Total size of 120x CSV files is only about 60-70kb so not much data really
// I wonder if I need to repeat the code? I am sure I could use a loop?
// I wonder too if I am mixing strings with arrays accidentally

?>
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    OK, thanks to ratmalwer I figured out how to create some more efficient code. He did 99% of it (had a couple of typos but that's fine)

    My new code below. I added str_replace("&amp","&",$url) to hopefully take care of those odd occasions whereby I end up with "&amp" instead of "&" in my $url. I will run the script for a few hours and see if I get any HTTP request failed! errors.

    Hope this helps someone else: "Grab (specified) data from certain columns in a Public Google Sheet and save it as a CSV file in "a location". But this cycles through multiple columns and multiple sheets (tabs in the same Google Sheet)

    <?php
// GLOBAL VARIABLES
$fileid = "2PACX-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"; // Google Sheet ID from File--> Share--> Publish to Web. (Must be this ID, not the ID from the browser URL)
$save_location_Data = "/home/[ my-site ]/wp-content/uploads/database"; // where I am saving the CSV files on my servers
$datatype = ['Teams', 'Stats', 'Games_1', ...[ AND SO ON]];
$range = ['range=A1:B200', 'range=C1:H200', 'range=I1:J200', ...[ AND SO ON]];
// $datatype and $range go hand in hand.... EG: My Teams list in in range A1:B200 of my Google Sheet, the [Team] stats are in range C1:H200 etc.

$division = "MEN"; // Unique part of filename... EG: MEN-Teams.csv , MEN-Stats.csv
$sheet_[$division] = "673108837"; // Google Sheet, Tab ID
foreach( $datatype as $index => $datatype_t ) {
    $url = str_replace("&amp","&","https://docs.google.com/spreadsheets/d/e/".$fileid."/pub?gid=".$sheet."&".$range[$index]."&single=true&output=csv"); // Building normal URL from above variables. I added str_replace to try and take care of those few HTTP errors I was getting... replacing &amp with & if there are any. If none, I assume it won't do anything.
    $filename_temp = $save_location_Data."/".$division."-".$datatype_t."_temp.csv"; // var for Temporary file.
    file_put_contents($filename_temp, file_get_contents($url)); // create temp file from URL
    clearstatcache(); // I believe this is good practice to clear the cache?
    $filename_final = $save_location_Data."/".$division."-".$datatype_t.".csv"; // var for final filename.
    if(filesize($filename_temp) != 0) {
        rename($filename_temp, $filename_final); // if file is not 0 [bytes], rename from temp to final.
        }
        else {
            unlink($filename_temp); // if file is 0, just delete it. I add this as I assume sometimes there might to comms errors between my server and Google. If so, I just don't want the data. I would rather keep the data I capctured 5min ago.
    }
}

// Added next data set for clarity...

$division = "WOMEN"; // Unique part of filename... EG: MEN-Teams.csv , MEN-Stats.csv
$sheet_[$division] = "2090442919"; // Google Sheet, Tab ID
foreach( $datatype as $index => $datatype_t ) {
    $url = str_replace("&amp","&","https://docs.google.com/spreadsheets/d/e/".$fileid."/pub?gid=".$sheet."&".$range[$index]."&single=true&output=csv"); // Building normal URL from above variables. I added str_replace to try and take care of those few HTTP errors I was getting... replacing &amp with & if there are any. If none, I assume it won't do anything.
    $filename_temp = $save_location_Data."/".$division."-".$datatype_t."_temp.csv"; // var for Temporary file.
    file_put_contents($filename_temp, file_get_contents($url)); // create temp file from URL
    clearstatcache(); // I believe this is good practice to clear the cache?
    $filename_final = $save_location_Data."/".$division."-".$datatype_t.".csv"; // var for final filename.
    if(filesize($filename_temp) != 0) {
        rename($filename_temp, $filename_final); // if file is not 0 [bytes], rename from temp to final.
        }
        else {
            unlink($filename_temp); // if file is 0, just delete it. I add this as I assume sometimes there might to comms errors between my server and Google. If so, I just don't want the data. I would rather keep the data I capctured 5min ago.
    }
}

// Repeats until I capture all $divison and $datatype...

?>

  2. 0

    I simplified your code a little bit.
    It should do the same as your code – but it is not tested!
    Please try if it works.

    <?php

/// Global variables
$fileid = "2PACX-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"; // Google Sheet ID from File--> Share--> Publish to Web.
$save_location_Data = "/home/[ my-site ]/wp-content/uploads/database"; // where I am saving the CSV files on my server

/**
 * MEN.
 */
$division = "MEN";
$sheet = "673108837"; // Google Sheet, Tab ID

$datatype[];    //make empty array
$range[];
$datatype[0] = "Teams"
$range[0] = "range=A1:B200"
$datatype[1] = "Stats";
$range[1] = "range=C1:H200";
$datatype[2] = "Games_1";
$range[2] = "range=I1:J200";
//you could use arraypush -> so you do not need declaring the arrayindex
//eg..
//array_push($datatype, "Games_99");
//array_push($range, "range=X1:YJ200");

//atlernatively you could use a multidimensional array - but for now we use 2 arrays and make suer the indexes corespond

for ($i=0; $i < count($datatype); $i++) {
    $url = "https://docs.google.com/spreadsheets/d/e/".$fileid."/pub?gid=".$sheet."&".$range[i]."&single=true&output=csv";
    $filename = $save_location_Data."/".$division."-".$datatype[i]."_temp_.csv";
    file_put_contents($filename, file_get_contents($url));
    clearstatcache();
    if(filesize($filename) != 0) {
        rename($filename."/".$division."-".$datatype[i].".csv"); 
        }
        else {
            unlink($filename);
    }
}
?>
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search