Posted on Leave a comment

How to Insert data in Google Spreadsheet using PHP?

Source: http://karl.kranich.org/2015/04/16/google-sheets-api-php/

Google has been sending out messages saying that several of their APIs are going to be discontinued (for example, the Document List API).  As a result, I tried to modify my custom Google Spreadsheet-populating PHP script to use the new Drive API.  After way too much wasted time, I discovered that the Drive API can create sheets and read their metadata, but can’t add rows — for that we can keep using the Spreadsheets API.

I was successful at converting the authentication portion of my script from the old ClientLogin to OAuth with a Service Account (the script runs behind the scenes of a web site and populates a Google spreadsheet that the web site user has no knowledge of).

It wasn’t as simple as I’d hoped because the Google-provided PHP client for the Drive API doesn’t know about the Spreadsheets API.  I had to dig around the PHP libraries to piece together authentication codes and Google_Http_Requests.

So, if anyone else out there is interested in the combination of PHP, the Spreadsheets API, and OAuth with Service Accounts, this could save you some time.

The code is available in a public Gist at https://gist.github.com/karlkranich/cbbfbdf5825217b2976f

Here are the step-by-step instructions to get this to work:

Create the Service Account

  1. Browse to https://console.developers.google.com
  2. Click “Create Project”, make up a name, and click “Create”
  3. Click “API” in the left column, then click “Drive API” and then “Enable API”. This is not needed for the Spreadsheets API, but you might want to use the Drive API also.
  4. Click “Credentials” in the left column.
  5. In the OAuth section, click “Create new Client ID”
  6. Choose “Service Account” and click “Create Client ID”
  7. Save the generated json file
  8. Click “Generate new P12 key”, save the file, and note the password on the screen.
  9. Note the Client ID and Email address – we’ll use them later

Create a Spreadsheet and share with the Service Account

  1. Create a Google Spreadsheet to play with
  2. Share the Google sheet with the gserviceacount email address from above (give edit permissions).

Get the PHP Drive API Client

git clone https://github.com/google/google-api-php-client.git google-api-php-client

Modify and run sheets-api-test.php

  1. Get the script from https://gist.github.com/karlkranich/cbbfbdf5825217b2976f
  2. Fill in the Client ID and Email address from the Google Developer Console
  3. Fill in the File ID of your spreadsheet from the URL in Chrome (see image below)
  4. Uncomment various sections of the script to see how they work

Let me know if any of this doesn’t work or doesn’t make sense!

file-id

 

<?php
session_start();
include_once "google-api-php-client/examples/templates/base.php";
/************************************************
  Make an API request authenticated with a service
  account.
 ************************************************/
require_once realpath(dirname(__FILE__) . '/google-api-php-client/src/Google/autoload.php');
$client_id = 'get your own from google developer console';
$service_account_name = 'get your own from google developer console';  // email address
$key_file_location = 'download your own.p12'; //key.p12
echo pageHeader("Service Account Access");
if (strpos($client_id, "googleusercontent") == false
    || !strlen($service_account_name)
    || !strlen($key_file_location)) {
  echo missingServiceAccountDetailsWarning();
  exit;
}
$client = new Google_Client();
$client->setApplicationName("Sheets API Testing");
$service = new Google_Service_Drive($client);
/************************************************
  If we have an access token, we can carry on.
  Otherwise, we'll get one with the help of an
  assertion credential. In other examples the list
  of scopes was managed by the Client, but here
  we have to list them manually. We also supply
  the service account
 ************************************************/
if (isset($_SESSION['service_token'])) {
  $client->setAccessToken($_SESSION['service_token']);
}
$key = file_get_contents($key_file_location);
$cred = new Google_Auth_AssertionCredentials(
    $service_account_name,
    array('https://www.googleapis.com/auth/drive','https://spreadsheets.google.com/feeds'),
    $key
);
$client->setAssertionCredentials($cred);
if ($client->getAuth()->isAccessTokenExpired()) {
  $client->getAuth()->refreshTokenWithAssertion($cred);
}
$_SESSION['service_token'] = $client->getAccessToken();
// Get access token for spreadsheets API calls
$resultArray = json_decode($_SESSION['service_token']);
$accessToken = $resultArray->access_token;
// The file ID was copied from a URL while editing the sheet in Chrome
$fileId = '15byt2tfdaHmaEpdwd4UYGWs70Eaej8edkQ2dS8x4mIk';
// Section 1: Uncomment to get file metadata with the drive service
// This is also the service that would be used to create a new spreadsheet file
// $results = $service->files->get($fileId);
// var_dump($results);
// Section 2: Uncomment to get list of worksheets
// $url = "https://spreadsheets.google.com/feeds/worksheets/$fileId/private/full";
// $method = 'GET';
// $headers = ["Authorization" => "Bearer $accessToken"];
// $req = new Google_Http_Request($url, $method, $headers);
// $curl = new Google_IO_Curl($client);
// $results = $curl->executeRequest($req);
// echo "$results[2]\n\n";
// echo "$results[0]\n";
// Section 3: Uncomment to get the table data
// $url = "https://spreadsheets.google.com/feeds/list/$fileId/od6/private/full";
// $method = 'GET';
// $headers = ["Authorization" => "Bearer $accessToken", "GData-Version" => "3.0"];
// $req = new Google_Http_Request($url, $method, $headers);
// $curl = new Google_IO_Curl($client);
// $results = $curl->executeRequest($req);
// echo "$results[2]\n\n";
// echo "$results[0]\n";
// Section 4: Uncomment to add a row to the sheet
$url = "https://spreadsheets.google.com/feeds/list/$fileId/od6/private/full";
$method = 'POST';
$headers = ["Authorization" => "Bearer $accessToken", 'Content-Type' => 'application/atom+xml'];
$postBody = '<entry xmlns="http://www.w3.org/2005/Atom" xmlns:gsx="http://schemas.google.com/spreadsheets/2006/extended"><gsx:gear>more gear</gsx:gear><gsx:quantity>99</gsx:quantity></entry>';
$req = new Google_Http_Request($url, $method, $headers, $postBody);
$curl = new Google_IO_Curl($client);
$results = $curl->executeRequest($req);
echo "$results[2]\n\n";
echo "$results[0]\n";

 

Source: http://karl.kranich.org/2015/04/16/google-sheets-api-php/

 

Posted on Leave a comment

How to move an array Element to any other index?

<?php

$array = array('a', 'b', 'c', 'd');
function move_array_element(&$array, $element_index, $required_index) 
{
    $out = array_splice($array, $element_index, 1);
    array_splice($array, $required_index, 0, $out);
}

 
// Let's say I want to move Element "d" to index 2
// So first I'll find it's index dynamically 
$element_index =array_search("d", $array);
$required_index = 1;                                           

move_array_element($array, $element_index, $required_index);

print_r($array); 

/*
 
 Result: Array ( [0] => a [1] => d [2] => b [3] => c )

*/

 

Posted on Leave a comment

how to create custom admin page?

In this article I’ll show how to create custom page for admin panel.

<?php

// Following code is just example to show how this works. So obviously you can alter this or write your own code for UI

// function that will prpeare User Interface for our New Admin Page
function az_admin_custom_page() // You can set any name whatever you want 
{
  global $wpdb; // this is required so that you can use wordpress to execute your sql queries

  $sql="SELECT *  FROM table";
  $rows= $wpdb->get_results($sql);

?>
   <div class="wrap">
        <h2><span class="dashicons dashicons-admin-comments"></span> Page Heding</h2> 
        <form method="POST" action="">
            <table class="wp-list-table widefat fixed">
                <thead>
                  <tr>
                    <th scope="col" id="username" class="manage-column column-username" style="">
                       Name  
                    </th>
                    <th scope="col" id="account" class="manage-column column-account" style="">Comment</th>
                    <th style="width:20%" scope="col" id="cb" class="manage-column column-cb check-column" style="">
                      Rating
                    </th>
                    <th>Class</th>
                    <th>Merchant</th>
                    <th style="width:10%">

                    </th>
                    
                  </tr>
				        </thead>
              <tbody id="the-list">
                <?php
                foreach($rows as $review)
                {


                ?>            
                <tr id="review_<?php echo $review->id;?>" class="<?php echo $review->id_post;?>">
                  <td><?php echo $review->name;?></td>
                  <td><?php echo $review->comment;?></td>
                  <td><?php echo $review->rate;?>/5</td>
                  <td><?php echo $review->class;?></td>
                  <td><?php echo $review->merchant;?></td>
                  <td><a data-id="<?php echo $review->id;?>" class="delete" href="javascript:void(0)">Delete</a></td>
                </tr>
                <?php
              }
              ?>
              </tbody>
            </table>
   <?php
                    
}

 

Posted on Leave a comment

How to setup Google reCaptcha Version 2.0?

Screenshot_4

To setup Google reCaptcha oepn url https://www.google.com/recaptcha/admin and fill form as shown in the screenshot below:

After registration open newly created reCaptcha site and read the guide provided by google as shown below:

Screenshot_3

Now paste <script src=’https://www.google.com/recaptcha/api.js’></script>  in <head> or anywhere before use of captcha.

And then paste <div class=”g-recaptcha” data-sitekey=”6LcPQggTAAAAAAw-SQm8KTEW-N1CBuzBz_gW71UT”></div> to the place where you want to show captcha in form and obviously you will have to use tag with your own site key from google reCaptcha site.

Continue reading How to setup Google reCaptcha Version 2.0?

Posted on Leave a comment

WordPress: How to submit custom form data using Ajax?

In wordpress we send data using same ajax method as we do in regular non-wordpress website.  The difference is few additional data variables and filter on serverside.

To Implement this I created a template page named “Request a Quote” in request-a-quote.php, created new wordpress and assigned that template to this page.

Screenshot_1

Continue reading WordPress: How to submit custom form data using Ajax?

Posted on Leave a comment

PHP: How to search and replace string from complete database using PHP

If you want to search and replace string from entire database (from all tables of any database) using php then use following code.

<?php
// Find and replace facility for complete MySQL database
//
// Written by Mark Jackson @ MJDIGITAL
// Can be used by anyone - but give me a nod if you do!
// http://www.mjdigital.co.uk/blog

// SEARCH FOR
$search        = '**__FIND_THIS__**';

// REPLACE WITH
$replace    = '**__REPLACE_WITH_THIS__**'; // (used if queryType below is set to 'replace')

// DB Details
$hostname = "__DB_HOST__";
$database = "__DB_DATABASE__";
$username = "__DB_USER__";
$password = "__DB_PASSWORD__";

// Query Type: 'search' or 'replace'
$queryType = 'replace';

// show errors (.ini file dependant) - true/false
$showErrors = true;

//////////////////////////////////////////////////////
//
//        DO NOT EDIT BELOW
//
//////////////////////////////////////////////////////

if($showErrors) {
    error_reporting(E_ALL);
    ini_set('error_reporting', E_ALL);
    ini_set('display_errors',1);
}

// Create connectio to DB
$MJCONN = mysql_pconnect($hostname, $username, $password) or trigger_error(mysql_error(),E_USER_ERROR);
mysql_select_db($database,$MJCONN);

// Get list of tables
$table_sql = 'SHOW TABLES';
$table_q = mysql_query($table_sql,$MJCONN) or die("Cannot Query DB: ".mysql_error());
$tables_r = mysql_fetch_assoc($table_q);
$tables = array();

do{
    $tables[] = $tables_r['Tables_in_'.strtolower($database)];
}while($tables_r = mysql_fetch_assoc($table_q));

// create array to hold required SQL
$use_sql = array();

$rowHeading = ($queryType=='replace') ? 
        'Replacing \''.$search.'\' with \''.$replace.'\' in \''.$database."'\n\nSTATUS    |    ROWS AFFECTED    |    TABLE/FIELD    (+ERROR)\n"
      : 'Searching for \''.$search.'\' in \''.$database."'\n\nSTATUS    |    ROWS CONTAINING    |    TABLE/FIELD    (+ERROR)\n";

$output = $rowHeading;

$summary = '';

// LOOP THROUGH EACH TABLE
foreach($tables as $table) {
    // GET A LIST OF FIELDS
    $field_sql = 'SHOW FIELDS FROM '.$table;
    $field_q = mysql_query($field_sql,$MJCONN);
    $field_r = mysql_fetch_assoc($field_q);

    // compile + run SQL
    do {
        $field = $field_r['Field'];
        $type = $field_r['Type'];

        switch(true) {
            // set which column types can be replaced/searched
            case stristr(strtolower($type),'char'): $typeOK = true; break;
            case stristr(strtolower($type),'text'): $typeOK = true; break;
            case stristr(strtolower($type),'blob'): $typeOK = true; break;
            case stristr(strtolower($field_r['Key']),'pri'): $typeOK = false; break; // do not replace on primary keys
            default: $typeOK = false; break;
        }

        if($typeOK) { // Field type is OK ro replacement
            // create unique handle for update_sql array
            $handle = $table.'_'.$field;
            if($queryType=='replace') {
                $sql[$handle]['sql'] = 'UPDATE '.$table.' SET '.$field.' = REPLACE('.$field.',\''.$search.'\',\''.$replace.'\')';
            } else {
                $sql[$handle]['sql'] = 'SELECT * FROM '.$table.' WHERE '.$field.' REGEXP(\''.$search.'\')';
            }

            // execute SQL
            $error = false;
            $query = @mysql_query($sql[$handle]['sql'],$MJCONN) or $error = mysql_error();
            $row_count = @mysql_affected_rows() or $row_count = 0;

            // store the output (just in case)
            $sql[$handle]['result'] = $query;
            $sql[$handle]['affected'] = $row_count;
            $sql[$handle]['error'] = $error;

            // Write out Results into $output
            $output .= ($query) ? 'OK        ' : '--        ';
            $output .= ($row_count>0) ? '<strong>'.$row_count.'</strong>            ' : '<span style="color:#CCC">'.$row_count.'</span>            ';
            $fieldName = '`'.$table.'`.`'.$field.'`';
            $output .= $fieldName;
            $erTab = str_repeat(' ', (60-strlen($fieldName)) );
            $output .= ($error) ? $erTab.'(ERROR: '.$error.')' : '';

            $output .= "\n";
        }
    }while($field_r = mysql_fetch_assoc($field_q));
}

// write the output out to the page
echo '<pre>';
echo $output."\n";
echo '<pre>';
?>

Just use your database credentials and it will work like charm.

Credits: Mark Jackson