Download a series of Google Docs (.csv files) to my site using PHP
My conundrum is as follows. I have a lot of data stored in various spreadsheets on Google Docs. Compiling all of the data into a single spreadsheet (for analysis purposes) hit the size limit. Seeing as that is out, I needed another method.
I wrote an Apps Script to take the data and export it all to a series of corresponding .csv files. I now need to get the data from those 30 .csv files stored in Google Docs and import it into a MySQL server. Is this the most efficient method?
Right now, I need a way to import all .csv files from my Google Docs List to the server, where my PHP script can import them to the database. Can you point me in the right direction?
I discovered the following post during my research, which seems pretty close, but it's unfortunately way above my head when it comes to adapting it to my purposes...
http://gdatatips.blogspot.com/2009/07/download-google-doc-using-php-library.html
function download($client, $url, $format=null) {
$sessionToken = $client->getHttpClient()->getAuthSubToken();
$opts = array(
'http' => array(
'method' =>开发者_运维百科; 'GET',
'header' => "GData-Version: 3.0\r\n".
"Authorization: AuthSub token=\"$sessionToken\"\r\n"
)
);
if ($url != null) {
$url = $url . "&exportFormat=$format";
}
return file_get_contents($url, false, stream_context_create($opts));
}
// TODO 1: setup a Zend_Gdata_Docs client in $docs_client
// TODO 2: fetch a $feed or $entry
$contentLink = $feed->entries[0]->content->getSrc();
$fileContents = download($docs_client, $contentLink, 'txt');
echo 'Contents of document "' . $feed->entries[0]->title . '":<hr>';
echo "<pre>$fileContents</pre>";
Thanks very much! Any assistance would be greatly appreciated! :)
I would download the .csv files using a batch file that is kicked off from scheduled tasks -(i.e. once per day) - then in the batch file kick off SQL c:\sql\update_timekeeping.sql The SQl commands stored in the update_timekeeping.sql text file. Also provides history of running the timekeeping update....
It seems to me that file_get_contents()
should be similar to the PHP version of this script
function file_get_contents(){
//This function generates a pdf of your current spreadsheet and emails it to yourself as attachment
//Make sure your spreadsheet is not too big. The pdf size tends to be 200kb/page and if too large
//if the pdf is too large the urlFetch might timeout
var AUTH_TOKEN = "xxxxxxxxxxxxxx"; //Enter your AUTH_TOKEN
//You can receive it from https://appscripts.appspot.com/getAuthToken
var ssID=SpreadsheetApp.getActiveSpreadsheet().getId()+"&gid="+SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getSheetId();
var url = "https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key="
+ ssID + "&exportFormat=csv";
//Add &gid=x at the end of above url if you only want a particular sheet
//gid of a sheet can be obtained by the undocumented function getSheetId()
//ex: SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getSheetId();
//exportFormat=xls did not work when I tried. I dont know why
var auth = "AuthSub token=\"" + AUTH_TOKEN + "\"";
var res = UrlFetchApp.fetch(url, {headers: {Authorization: auth}});
var content=res.getContentText();
return content
}
精彩评论