Category Archives: Php

How to write to Google Sheet with API in PHP

Wednesday, 16 March 2022 15:28

Tim Ramsey

0 Comments

Get a service account key from https://console.cloud.google.com/apis/credentials (You can here also restrict what this keys is allowed todo)
1. When creating, make sure you click the Furnish a new private key
2. Select JSON when it asks you how to download the key.
The service account key you have just generated includes a client_email.
1. Go to you google spreadsheet and allow this client_email to have write access on this document

$sheet_name = "Sheet1";
$google_client = new \Google_Client();
$google_client->setApplicationName('Google Sheets Updater');
$google_client->setScopes([\Google_Service_Sheets::SPREADSHEETS]);
$google_client->setAuthConfig(storage_path('jsondownload.json'));
$service = new \Google_Service_Sheets($google_client);
$spreadsheet_id = 'your_sheet_id';
$range = $sheet_name.'!A:C';
$params = [
   'valueInputOption' => "USER_ENTERED"
];
$value_array = ["1","2","3","4","5","6"];
$body = new \Google_Service_Sheets_ValueRange([
   'values' => [$value_array]
]);
$service->spreadsheets_values->append($spreadsheet_id, $range, $body, $params);

$sheet_name = "Sheet1";

$google_client = new \Google_Client();

$google_client->setApplicationName('Google Sheets Updater');

$google_client->setScopes([\Google_Service_Sheets::SPREADSHEETS]);

$google_client->setAuthConfig(storage_path('jsondownload.json'));

$service = new \Google_Service_Sheets($google_client);

$spreadsheet_id = 'your_sheet_id';

$range = $sheet_name.'!A:C';

$params = [

'valueInputOption' => "USER_ENTERED"

];

$value_array = ["1","2","3","4","5","6"];

$body = new \Google_Service_Sheets_ValueRange([

'values' => [$value_array]

]);

$service->spreadsheets_values->append($spreadsheet_id, $range, $body, $params);

https://stackoverflow.com/questions/44045183/access-google-spreadsheet-api-without-auth-token

Excel Compatible Partnumbers

Tuesday, 14 September 2021 15:51

Tim Ramsey

0 Comments

$compatible_partnums = [];
//param &$compatible_partnums

//Add the shared identifier and the unique identifier
if (array_key_exists('hardwarenum', $items)) {
    $compatible_partnums[$items['hardwarenum']][] = $items['partnum'];
}

$compats = array_map(function($k, $v) {
    return [$k, implode(', ', $v)];
}, array_keys($compatible_partnums), $compatible_partnums);
Excel::create($filename.
        '-hw',
        function($excel) use($newArray, $compats) {
            $excel - > sheet('Sheetname', function($sheet) use($newArray, $compats) {
                $sheet - > fromArray($compats);
            });
        }) - >
    export ('csv');

$compatible_partnums = [];

//param &$compatible_partnums

//Add the shared identifier and the unique identifier

if (array_key_exists('hardwarenum', $items)) {

$compatible_partnums[$items['hardwarenum']][] = $items['partnum'];

}

$compats = array_map(function($k, $v) {

return [$k, implode(', ', $v)];

}, array_keys($compatible_partnums), $compatible_partnums);

Excel::create($filename.

'-hw',

function($excel) use($newArray, $compats) {

$excel - > sheet('Sheetname', function($sheet) use($newArray, $compats) {

$sheet - > fromArray($compats);

});

}) - >

export ('csv');

WHMCS Oauth userinfo.php not working

Wednesday, 21 July 2021 17:07

Tim Ramsey

0 Comments

I was trying to incorporate SSO in Freshdesk with WHMCS, but because WHMCS requires passing a query parameter = access_token instead of using the Authorization header, this is what you need to do to make it work.

1. Reveal Authorization Header in .htaccess

CGIPassAuth on

1	CGIPassAuth on

2. Use your own oauth-intermediary.php where you take the header and send the query parameter that WHMCS is expecting

<?php
//https://www.yousite.com/oauth/userinfo.php
//https://www.yousite.com/oauth-intermediary.php
header('Content-Type: application/json');
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL,"https://www.youtsite.com/oauth/userinfo.php?access_token=".str_replace("Bearer ","",getallheaders()["Authorization"]));
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
$server_output = curl_exec($ch);
echo $server_output;
curl_close ($ch);

<?php

//https://www.yousite.com/oauth/userinfo.php

//https://www.yousite.com/oauth-intermediary.php

header('Content-Type: application/json');

$ch = curl_init();

curl_setopt($ch, CURLOPT_URL,"https://www.youtsite.com/oauth/userinfo.php?access_token=".str_replace("Bearer ","",getallheaders()["Authorization"]));

curl_setopt($ch, CURLOPT_POST, 1);

curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

$server_output = curl_exec($ch);

echo $server_output;

curl_close ($ch);

3. User your new https://www.yousite.com/oauth-intermediary.php as the new userinfo.php URL when requested

Loop Spreadsheet and Update image height/width

Friday, 14 May 2021 19:13

Tim Ramsey

0 Comments

$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load(storage_path('file.xlsx'));
        $worksheet = $spreadsheet->getActiveSheet();
        $highestRow = $worksheet->getHighestRow();
        for($row = 1; $row <= $highestRow; ++$row){;
            $value = $worksheet->getCellByColumnAndRow(23, $row)->getValue();
            $doc = new \DOMDocument();
            @$doc->loadHTML('<node>' . $value .'</node>', LIBXML_HTML_NOIMPLIED | LIBXML_HTML_NODEFDTD);
            $images = $doc->getElementsByTagName('img');
            foreach ($images as $image) {
                if(!$image->hasAttribute('height') || $image->hasAttribute('width')){
                    $src = $image->getAttribute('src');
                    list($width, $height, $type, $attr) = @getimagesize($src);
                    if((int) $height > 0) {
                        $image->setAttribute('height', $height);
                    }
                    if((int) $width > 0) {
                        $image->setAttribute('width', $width);
                    }
                }
            }
            $document = utf8_decode($doc->saveHTML($doc->documentElement));
            $worksheet->setCellValueByColumnAndRow(23, $row, str_replace(array('<node>','</node>') , '' , $document));
        }
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $writer->save(storage_path('file-new.xlsx'));

$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load(storage_path('file.xlsx'));

$worksheet = $spreadsheet->getActiveSheet();

$highestRow = $worksheet->getHighestRow();

for($row = 1; $row <= $highestRow; ++$row){;

$value = $worksheet->getCellByColumnAndRow(23, $row)->getValue();

$doc = new \DOMDocument();

@$doc->loadHTML('<node>' . $value .'</node>', LIBXML_HTML_NOIMPLIED | LIBXML_HTML_NODEFDTD);

$images = $doc->getElementsByTagName('img');

foreach ($images as $image) {

if(!$image->hasAttribute('height') || $image->hasAttribute('width')){

$src = $image->getAttribute('src');

list($width, $height, $type, $attr) = @getimagesize($src);

if((int) $height > 0) {

$image->setAttribute('height', $height);

}

if((int) $width > 0) {

$image->setAttribute('width', $width);

}

$document = utf8_decode($doc->saveHTML($doc->documentElement));

$worksheet->setCellValueByColumnAndRow(23, $row, str_replace(array('<node>','</node>') , '' , $document));

}

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);

$writer->save(storage_path('file-new.xlsx'));

M2E additional columns in grid

Wednesday, 29 May 2019 17:20

Tim Ramsey

0 Comments

app/code/community/Ess/M2ePro/Block/Adminhtml/Ebay/Listing/View/Ebay/Grid
app/code/community/Ess/M2ePro/Block/Adminhtml/Ebay/Listing/Product
protected function _prepareCollection(){
	$collection->addAttributeToSelect('asset_type');
}

protected function _prepareColumns(){
	$attribute = Mage::getModel('eav/config')->getAttribute('catalog_product', 'asset_type');
	$options = array();
	foreach( $attribute->getSource()->getAllOptions(false, true) as $option ) {
		$options[$option['value']] = $option['label'];
	}
	$this->addColumn('asset_type',array(
		'header'=> Mage::helper('M2ePro')->__('Dept.'),
		'width' => '150px',
		'index' => 'asset_type',
		'type'  => 'options',
		'options' => $options,
		'sortable' => false,
	));

	$sets = Mage::getResourceModel('eav/entity_attribute_set_collection')
				->setEntityTypeFilter(Mage::getModel('catalog/product')->getResource()->getTypeId())
				->load()
				->toOptionHash();
	$this->addColumn('set_name',
		array(
			'header'=> Mage::helper('M2ePro')->__('Attrib. Set Name'),
			'width' => '100px',
			'index' => 'attribute_set_id',
			'type'  => 'options',
			'options' => $sets,
			'sortable' => false,
	), 'name'); 
}

app/code/community/Ess/M2ePro/Block/Adminhtml/Ebay/Listing/View/Ebay/Grid

app/code/community/Ess/M2ePro/Block/Adminhtml/Ebay/Listing/Product

protected function _prepareCollection(){

$collection->addAttributeToSelect('asset_type');

}

protected function _prepareColumns(){

$attribute = Mage::getModel('eav/config')->getAttribute('catalog_product', 'asset_type');

$options = array();

foreach( $attribute->getSource()->getAllOptions(false, true) as $option ) {

$options[$option['value']] = $option['label'];

}

$this->addColumn('asset_type',array(

'header'=> Mage::helper('M2ePro')->__('Dept.'),

'width' => '150px',

'index' => 'asset_type',

'type' => 'options',

'options' => $options,

'sortable' => false,

));

$sets = Mage::getResourceModel('eav/entity_attribute_set_collection')

->setEntityTypeFilter(Mage::getModel('catalog/product')->getResource()->getTypeId())

->load()

->toOptionHash();

$this->addColumn('set_name',

array(

'header'=> Mage::helper('M2ePro')->__('Attrib. Set Name'),

'width' => '100px',

'index' => 'attribute_set_id',

'type' => 'options',

'options' => $sets,

'sortable' => false,

), 'name');

}

Page 1 of 2

Category Archives: Php

How to write to Google Sheet with API in PHP

Excel Compatible Partnumbers

WHMCS Oauth userinfo.php not working

Loop Spreadsheet and Update image height/width

M2E additional columns in grid

Categories

Recent Posts