Import From Google Sheets

importFromGoogleSheetsCommand.php

<?php

namespace App\Command;

use App\Entity\Property;
use App\Entity\PropertyRepository;
use Doctrine\ORM\EntityManagerInterface;
use Google_Client;
use Google_Exception;
use Google_Service_Sheets;
use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputArgument;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Input\InputOption;
use Symfony\Component\Console\Output\OutputInterface;
use Symfony\Component\Console\Style\SymfonyStyle;

class importManagementPropertyInfoCommand extends Command
{
    protected static $defaultName = 'app:importFromGoogleSheetsCommand';
    /**
     * @var EntityManagerInterface
     */
    private $em;
    /**
     * @var SymfonyStyle
     */
    private $io;

    /** @var Google_Service_Sheets */
    private $sheets;

    /**
     * importManagementPropertyInfoCommand constructor.
     * @param EntityManagerInterface $entityManager
     * @throws Google_Exception
     */
    public function __construct(EntityManagerInterface $entityManager)
    {
        $this->em = $entityManager;

        $client = new Google_Client();
        $client->setAccessType('offline');
        $client->setApplicationName('ImportFromGoogleSheets');
        $client->setScopes([\Google_Service_Sheets::SPREADSHEETS]);
        $client->setAuthConfig('./config/google-api.json');

        $this->sheets = new Google_Service_Sheets($client);
        
        parent::__construct();
    }

    protected function configure()
    {
        $this
            ->addArgument('sheetId', InputArgument::REQUIRED, 'What is your sheet id. eg: XXXXXXXXXXXXXXXXXX')
            ->addArgument('range', InputArgument::OPTIONAL, 'Specify your range', 'Sheet1!A1:N')
        ;
    }

    protected function execute(InputInterface $input, OutputInterface $output): int
    {
        $this->io = new SymfonyStyle($input, $output);

        $sheetId = $input->getArgument('sheetId');
        if (strlen($sheetId) < 10){
            $this->io->error('Please specify sheet id. Eg: XXXXXXXXXX');
            return 404;
        }

        $range = $input->getArgument('range');
        $response = $this->sheets->spreadsheets_values->get($sheetId, $range);
        $values = $response->getValues();

        if (empty($values)) {
            $this->io->error('You cannot access the sheet');

            return 403;
        } else {
            $header = [];
            $data = [];

            foreach ($values as $rowId => $row) {
                if ($rowId === 0){
                    $header = $row;
                } else {
                    $rowData = [];
                    foreach ($row as $columnId => $value){
                        $rowData[$header[$columnId]] = $value;
                    }
                    $data[] = $rowData;
                }
            }

            unset($header);
        }

        $propertyMatches = [
            'Mgmt fee per room inc VAT' => 'setManagementFee',
            'British gas costs' => 'setBritishGasCost',
            'Bills cost per tenant' => 'setBillsCost'
        ];

        /** @var PropertyRepository $propertyRepository */
        $propertyRepository = $this->em->getRepository(Property::class);

        $updates = 0;

        foreach ($data as $rowId => $row){
            $property = $propertyRepository->find($row['Property ID']);

            if (!$property instanceof Property){
                $this->io->warning(sprintf('Property not found for row #%s', ($rowId+1)));
            } else {
                $currentUpdates = 0;
                foreach ($propertyMatches as $sheetHeader => $propertySetter){
                    if (!empty($row[$sheetHeader])){
                        $updates++;
                        $currentUpdates++;
                        $property->$propertySetter((float) str_replace(',', '', $row[$sheetHeader]));
                    } else {
                        $this->io->warning(sprintf('Can\'t find value for %s for property id #%s', $sheetHeader, $property->getId()));
                    }
                }

                if ($currentUpdates){
                    $this->em->persist($property);
                }
            }
        }

        if ($updates){
            $this->io->note(sprintf('Flushing %d changes', $updates));
            $this->em->flush();
            $this->io->success('All ok');
        }

        return 0;
    }
}