Google sheets import

 google-sheets-tips-update.json

"google/apiclient": "^2.0",

Route::get('excelImport', 'Admin\AdminController@excelImport')->name('excelImport');

{
  "type": "service_account",
  "project_id": "excel-sheet-demo",
  "private_key_id": "f452ed9f9e2345fc3a6793737254e59efb7dad7ff",
  "private_key": "-----BEGIN PRIVATE KEY-----END PRIVATE KEY-----\n",
  "client_email": "demo-account-excel-sheet@excel-sheet.com",
  "client_id": "105540755601386431894",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/.com"
}

    /**
     * update tips by google sheets.
     *
     * @param  GameTip $manage_tip
     * @return \Illuminate\Http\Response
    */
    public function excelImport(Request $request)
    {
        ini_set('max_execution_time', 180);

        $client = new \Google_Client();
        $client->setApplicationName('My PHP App');
        $client->setScopes([\Google_Service_Sheets::SPREADSHEETS]);
        $client->setAccessType('offline');
        // $client->setAuthConfig(__DIR__ . './../google-sheets-tips-update.json');

        $client->setAuthConfig(__DIR__ . '/google-sheets-tips-update.json');

  
        $sheets = new \Google_Service_Sheets($client);

        try {
  
        $spreadsheeturl = $request->spreadsheetId;
        $uri_segments = explode('/', $spreadsheeturl);

        $spreadsheetId = $uri_segments[5];

        $response = $sheets->spreadsheets->get($spreadsheetId);


        $fileTitle = $response['sheets'][0]['properties']['title'];
 
        if(!empty($spreadsheetId) && $uri_segments[4] =='d'){
            $range = $fileTitle.'!A1:Z';
           
            // try {
            $rows = $sheets->spreadsheets_values->get($spreadsheetId, $range);
            // } catch (\Exception $e) {
            //     return redirect(route('updateresult'))->with('error', trans('messages.manage_tips.updateGoogleSheets.error'));
            // }
            
            $values = $rows->getValues();
 
            $limitRecord = 100;
            $increaseRecord = 100;
            $totalRecord = count($values);
            $totalRecordCount = count($values);
            $totalRecord = $totalRecord / $limitRecord;
            $totalRecord =  ceil($totalRecord);
            $updateArray = array();
            $startRecord = 1;

            $userId =  \Auth::guard('superadmin')->user()->id;

            $spreadsheet = new Spreadsheet;
            $spreadsheet->spreadsheetId = $spreadsheeturl;
            $spreadsheet->packageId = $request->package;
            $spreadsheet->userId = $userId;

            $spreadsheet->save();

            for ($i = 0; $i < $totalRecord; $i++) {
                if ($limitRecord > $totalRecordCount) {
                    $range = $fileTitle.'!A' . $startRecord . ':Z' . $totalRecordCount . '';
                } else {
                    $range = $fileTitle.'!A' . $startRecord . ':Z' . $limitRecord . '';
                }

                $rows = $sheets->spreadsheets_values->get($spreadsheetId, $range);
                $values = $rows->getValues();

                if ($values) {
                    if($startRecord == 1 ){
                        unset($values[0]);
                        unset($values[1]);
                    }

                    $updateArray = array();
                    foreach ($values as $key => $val) {
                        if (!empty($val)) {

                           $updateArray[$key]['competition'] = isset($val[1]) ? $val[1] : '';
                            
                        }
                    }
                }

                if(!empty($updateArray) && count($updateArray) > 0 )
                {
                    \DB::table('games_tips')->insert($updateArray);
                    $updateArray = array();
                }

                $startRecord = $startRecord + $increaseRecord;
                $limitRecord = $limitRecord + $increaseRecord;
            }

            return redirect(route('updateresult'))->with('success', trans('messages.manage_tips.updateGoogleSheets.success'));
        }
        } catch (\Exception $e) {
            return redirect(route('updateresult'))->with('error', trans('messages.manage_tips.updateGoogleSheets.error'));
        }

        return redirect(route('updateresult'))->with('error', trans('messages.manage_tips.updateGoogleSheets.error'));
    }

Comments

Post a Comment

Popular posts from this blog

API

  <?php namespace  App\Http\Controllers\Api ; use App\Helpers\ ApiHelper ; use App\Helpers\ BraintreeHelper ; use App\Http\Controllers\ Controller ; use App\Model\ User ; use App\Model\ UserLogin ; use App\Model\ UserReferralCodes ; use App\Model\ UserPackagesSubscriptionHistories ; use App\Model\ SubscriptionHistories ; use App\Model\ SubscriptionPlan ; use App\Model\ Notification ; use App\Model\ UserMessage ; use Illuminate\Http\ Request ; use Illuminate\Support\Facades\ Auth ; use Illuminate\Support\Facades\ DB ; use Carbon\ Carbon ; class   AuthController   extends   Controller {      /*     |--------------------------------------------------------------------------     | Auth Controller     |--------------------------------------------------------------------------     | ...
Read more

Encryption and Decryption By PHP

<?php Encryption and Decryption By PHP  Stap : 1 Create Library In Codeigniter , Encryption.php Stap : 2 Call Library In Controller $this->load->library('encryption'); $encData ='This is Encryption Library ,Main use for URL Encryption and Decryption'; $enc =  $this->encryption->encryptor('encrypt', $encData); //Call for Encryption $dec =  $this->encryption->encryptor('decrypt', $enc); // Call for Decryption Encryption.php <?php class Encryption {     function encryptor($action, $string) {         $output = false;         $encrypt_method = "AES-256-CBC";         $secret_key = 'test';         $secret_iv = 'test123';         $key = hash('sha256', $secret_key);         $iv = substr(hash('sha256', $secret_iv), 0, 16);         if( $action == 'encrypt' ) {   ...
Read more

Seeder

 php artisan make:seeder UsersTableDataSeeder php artisan db:seed <?php use Illuminate\Database\Seeder; use App\Model\user; class UsersTableDataSeeder extends Seeder {     /**      * Run the database seeds.      *      * @return void      */     public function run()     {         for ($i=0; $i < 15; $i++) {              $pass = str_random(5);     user::create([                 'userType' => 'Admin',                 'name' => str_random(5),                 'email' => str_random(5).'@gmail.com',                 'password' => bcrypt($pass),                 'showPassword' => $pass,         ...
Read more