<?php

Class gSheet{
	private $spreadsheetId;
	private $service;
	private $sheettitle;
	
	function gSheet($link){
		$linkArr = explode('/',$link);
		$i = 0;
		$id = 0;
		foreach($linkArr as $item){
			$i++;
			if ($item == 'd'){
				$id = $i;
			}
		}
		$this->spreadsheetId = $linkArr[$id];
		
		$root_path = __DIR__.'/googleapi/';
		require_once $root_path.'vendor/autoload.php';
		$client = new Google_Client();
		putenv('GOOGLE_APPLICATION_CREDENTIALS='.$root_path.'MyProject-8602820efc85.json');

		$client = new Google_Client();
		$client->useApplicationDefaultCredentials();
		$client->setScopes(implode(' ', array(Google_Service_Sheets::SPREADSHEETS)));
		
		$this->service = new Google_Service_Sheets($client);
	}
	
	function read($range){
		$response = $this->service->spreadsheets_values->get($this->spreadsheetId, $range);
		$values = $response->getValues();
		
		return $values;
	}
	function readBackground($range){
		
		$response = $this->service->spreadsheets->get($this->spreadsheetId, array(
			'includeGridData' => true,
			'ranges'          => $range
		));
			
		$background = $response->getSheets()[0]->getData()[0]->getRowData()[0]->getValues()[0]->getEffectiveFormat()->getBackgroundColor();
		
		$bg = array(
			$background->red,
			$background->green,
			$background->blue
		);

		$r = base_convert(round($background->red*255), 10, 16);
		$g = base_convert(round($background->green*255), 10, 16);		
		$b = base_convert(round($background->blue*255), 10, 16);
		
		$bg = '#'.$r.$g.$b.'<br />';
		
				
		return $bg;
	}
	function CountCol(){
		$worksheet = $this->service->spreadsheets_values->get($this->spreadsheetId, $range);
		return $worksheet->getListFeed()->getEntries();
	}
	function writeRange($values, $range){
		$data = [];
		$data[] = new Google_Service_Sheets_ValueRange([
		  'range' => $range,
		  'values' => $values
		]);
		
		$body = new Google_Service_Sheets_BatchUpdateValuesRequest([
			"valueInputOption" => "USER_ENTERED",
			'data' => $data
		]);
		
		$result = $this->service->spreadsheets_values->batchUpdate($this->spreadsheetId, $body);
	}	
	function write($values, $row = 2, $sheet = false){
		if (!$sheet) $sheet = $this->sheettitle;
		$range = $sheet.'!A'.$row.':D';
		$data = [];
		$data[] = new Google_Service_Sheets_ValueRange([
		  'range' => $range,
		  'values' => $values
		]);
		
		$body = new Google_Service_Sheets_BatchUpdateValuesRequest([
			"valueInputOption" => "USER_ENTERED",
			'data' => $data
		]);
		
		$result = $this->service->spreadsheets_values->batchUpdate($this->spreadsheetId, $body);
	}
	function findSheet($stTitle){
		try { 
			$this->read($stTitle.'!A2:C');
			$response = true;
		}catch(Exception $e){
			$this->addSheet($stTitle);
			$response = false;
		} 
		
		return $response;
	}
	function findSheetId($stTitle){
		$response = $this->service->spreadsheets->get($this->spreadsheetId);
		$sheetId = false;
		foreach($response->getSheets() as $s) {
			if ($stTitle == $s['properties']['title'])
				$sheetId = $s['properties']['sheetId'];
		}
		
		return $sheetId;
	}
	function addSheet($stTitle){
		
		$this->sheettitle = $stTitle;
		
		$requests = [
			new Google_Service_Sheets_Request([
				'addSheet' => [
					'properties' => [
						"title"			=> $this->sheettitle,
						"gridProperties"=> [
							"columnCount"	=> 3
						]
					]
				]
			])
		];

		$requestBody = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
			'requests' => $requests
		]);
		$response = $this->service->spreadsheets->batchUpdate($this->spreadsheetId, $requestBody);

		$sheetId = $response->getReplies()[0]->getAddSheet()->getProperties()->sheetId;
		
		// formatting head
		$requests = [
			new Google_Service_Sheets_Request([
				'repeatCell'	=> [
					'range'	=> [
						'sheetId'	=> $sheetId,
						'startRowIndex'	=> 0,
						'endRowIndex'	=> 1
					],
					'cell'	=> [
						'userEnteredFormat'	=> [
							'textFormat'	=> [
								"bold"	=> true
							]
						]
					],
					"fields"	=> "userEnteredFormat(textFormat)"
				]
			]),
			new Google_Service_Sheets_Request([
				'updateSheetProperties' => [
					'properties' => [
						"sheetId"	=> $sheetId,
						"gridProperties"=> [
							"frozenRowCount"	=> 1
						],
					],
					"fields"	=> "gridProperties.frozenRowCount"
				]
			]),
			new Google_Service_Sheets_Request([
				'updateDimensionProperties' => [
					'range'	=> [
						'sheetId'		=> $sheetId,
						'dimension' 	=> "COLUMNS",
						'startIndex'	=> 1,
						'endIndex'		=> 3
					],
					'properties'	=> [
						'pixelSize'	=> 180
					],
					"fields"	=> "pixelSize"
				]
			]),
		];
		$requestBody = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
			'requests' => $requests
		]);
		$response = $this->service->spreadsheets->batchUpdate($this->spreadsheetId, $requestBody);


		// add head title
		$values = [
			['Ник', 'Дата', 'Ответ']
		];
		$this->write($values, 1);
	}
}