Skip to content

Instantly share code, notes, and snippets.

@Alzy

Alzy/export-magento-products-to-google-merchant-sheet

Created January 18, 2019 21:12
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save Alzy/05cb6bb1f7fe5dc304ffd40c4626a221 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save Alzy/05cb6bb1f7fe5dc304ffd40c4626a221 to your computer and use it in GitHub Desktop.
Download ZIP
This php script exports magento products to a google sheet to be used with google merchant.
Raw
export-magento-products-to-google-merchant-sheet
<?php
require(__DIR__ . '/../vendor/autoload.php');
require "../app/Mage.php";
umask(0);
Mage::app();
header("Content-type: text/plain");
if (php_sapi_name() != 'cli') {
throw new Exception('This application must be run on the command line.');
}
//////////////////////////
// GOOGLE API FUNCTIONS //
//////////////////////////
/**
* Returns an authorized API client.
* @return Google_Client the authorized client object
*/
function getClient()
{
$client = new Google_Client();
$client->setApplicationName('Probeauty Online - Google Merchant Feed');
$client->setScopes(Google_Service_Sheets::SPREADSHEETS);
$client->setAuthConfig('credentials.json');
$client->setAccessType('offline');
$client->setPrompt('select_account consent');
// Load previously authorized token from a file, if it exists.
// The file token.json stores the user's access and refresh tokens, and is
// created automatically when the authorization flow completes for the first
// time.
$tokenPath = 'token.json';
if (file_exists($tokenPath)) {
$accessToken = json_decode(file_get_contents($tokenPath), true);
$client->setAccessToken($accessToken);
}
// If there is no previous token or it's expired.
if ($client->isAccessTokenExpired()) {
// Refresh the token if possible, else fetch a new one.
if ($client->getRefreshToken()) {
$client->fetchAccessTokenWithRefreshToken($client->getRefreshToken());
// update our access token
echo "Update our access token using our refresh token";
$newAccessToken = $client->getAccessToken();
$accessToken = array_merge($accessToken, $newAccessToken);
file_put_contents($credentialsPath, json_encode($accessToken));
} else {
// Request authorization from the user.
$authUrl = $client->createAuthUrl();
printf("Open the following link in your browser:\n%s\n", $authUrl);
print 'Enter verification code: ';
$authCode = trim(fgets(STDIN));
// Exchange authorization code for an access token.
$accessToken = $client->fetchAccessTokenWithAuthCode($authCode);
$client->setAccessToken($accessToken);
// Check to see if there was an error.
if (array_key_exists('error', $accessToken)) {
throw new Exception(join(', ', $accessToken));
}
}
// Save the token to a file.
if (!file_exists(dirname($tokenPath))) {
mkdir(dirname($tokenPath), 0700, true);
}
file_put_contents($tokenPath, json_encode($client->getAccessToken()));
}
return $client;
}
///////////////////////
// MAGENTO FUNCTIONS //
///////////////////////
// filter function to check if there is a product match between google sheets and magento
function mergeProductArrays($mProducts, $gProducts)
{
$mergedArray = array();
$mergedIndexes = array();
// first merge what is in the google sheet.
foreach ($mProducts as $key => $mp) {
foreach ($gProducts as $gp) {
if($mp[0] == $gp[0]){
array_push($mergedIndexes, $key);
// quick patch
$item_group_id = $gp[1];
if($item_group_id == "p"){
$item_group_id = "";
}
array_push($mergedArray, array(
$mp[0],
$item_group_id,
$mp[2],
$mp[3],
$gp[4] ?: "",
$gp[5] ?: "",
$mp[6],
$mp[7],
$mp[8],
$mp[9],
$mp[10],
$mp[11],
$gp[12] ?: "",
$mp[13],
$gp[14] ?: "",
$gp[15] ?: "",
$gp[16] ?: "",
$gp[17] ?: "",
));
}
}
}
// Now push whatever was not in the google sheet before
foreach ($mProducts as $key => $mp) {
$skip = false;
foreach ($mergedIndexes as $index) {
if( $key == $index) {
$skip = true;
}
}
if($skip) {
continue;
}
else {
array_push($mergedArray, array(
$mp[0],
"",
$mp[2],
$mp[3],
"",
"",
$mp[6],
$mp[7],
$mp[8],
$mp[9],
$mp[10],
$mp[11],
"",
$mp[13],
"",
"",
"",
"",
));
}
}
return $mergedArray;
}
// return the text within the first p tag of a string
function getFirstPara($string){
$string = substr($string,0, strpos($string, "</p>")+4);
$string = str_replace("<p>", "", str_replace("<p/>", "", $string));
// convert html entity characters to their corresponding characters.
$string = html_entity_decode($string, ENT_QUOTES);
return $string;
}
/**
* returns the url for the parent product if there is one
*/
function getParentUrl($product){
// USED TO EFFECIENTLY RETURN PARENT URL FOR VARIENT PRODUCTS
$rewrite = Mage::getModel('core/url_rewrite');
$prodId = $product->getId();
$arrayOfParentIds = Mage::getSingleton("catalog/product_type_configurable")
->getParentIdsByChild($prodId);
$parentId = (count($arrayOfParentIds) > 0 ? $arrayOfParentIds[0] : null);
$idPath = 'product/' . $parentId;
$rewrite->loadByIdPath($idPath);
$parentUrl = Mage::getUrl($rewrite->getRequestPath(), false);
return substr(strtok($parentUrl, '?SID'), 0, -1);
}
//////////
// MAIN //
//////////
// Get the API client and construct the service object.
$client = getClient();
$service = new Google_Service_Sheets($client);
// Writes to the Google Docs spreadsheet:
// https://docs.google.com/spreadsheets/d/34e5jpFNqQBg4tZ5yq2puNe0t434XySNrUY23450ov_VpXxk/edit
// (replace with your own)
$spreadsheetId = '34e5jpFNqQBg4tZ5yq2puNe0t434XySNrUY23450ov_VpXxk';
$headerRange = 'A1:1';
$response = $service->spreadsheets_values->get($spreadsheetId, $headerRange);
$headerValues = $response->getValues();
$productsRange = '2:100000'; // Hopefully we never send more than 100,000 products... lol
$response = $service->spreadsheets_values->get($spreadsheetId, $productsRange);
$productsValues = $response->getValues();
// GET PRODUCTS AND EXPORT
$mProducts = Mage::getModel("catalog/product")->getCollection();
$mProducts->addFieldToFilter(
"in_google_merchant_feed", array("eq" => 1)
);
$mProducts->addAttributeToSelect(array(
"id",
"name",
"image",
"description",
"manufacturer",
"price",
"sku"
));
$magentoProducts = array();
foreach($mProducts as $product) {
$description = $product->getDescription();
$description = getFirstPara($description);
$description = strip_tags($description);
$condition = "new";
$price = (
"" .
number_format((float)$product->getPrice(), 2, '.', '') .
" USD"
);
$availability = "out of stock";
if ($product->getStockItem()->getIsInStock()){
$availability = "in stock";
}
$mpn = "";
if(Mage::getModel('catalog/product_type_configurable')->getParentIdsByChild($product->getId())) {
// variant
$url = getParentUrl($product);
} else {
// simple
$url = $product->getProductUrl(false);
}
$feedAttributes = array (
$product->getId(),
" ",
$product->getName(),
$description,
" ",
" ",
$condition,
$price,
$availability,
$url,
$product->getImageUrl(),
$product->getSku(),
$mpn,
$product->getAttributeText('manufacturer'),
);
array_push($magentoProducts, $feedAttributes);
}
////////////////////////////////////////////////////
// PUSH TO GOOGLE MERCHANT GOOGLE DOC SPREADSHEET //
////////////////////////////////////////////////////
$range = "2:100000";
// merge arrays
$finalProductsList = mergeProductArrays($magentoProducts, $productsValues);
// clear google spreadsheet
$clearRequestBody = new Google_Service_Sheets_ClearValuesRequest();
$service->spreadsheets_values->clear($spreadsheetId, $range, $clearRequestBody);
// set values and configuration
$body = new Google_Service_Sheets_ValueRange([
"values" => $finalProductsList
]);
$conf = ["valueInputOption" => "RAW"];
// Update the spreadsheet
$service->spreadsheets_values->update($spreadsheetId, $range, $body, $conf);
echo var_export($finalProductsList), "\n";
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment