How to import magento data to Google SpreadSheet

Software Engineer, React Training, Testing JavaScript Training

How to import magento data to Google SpreadSheet

This week, we just launched a function for client's Ecommerce site that show the contact form when user clicked on the call button out of a working hour ( that time, Call Center can't support and receive calls).

So we develop a form that receive a registration include name and phone number/email from visitors and automatically sync to Google SpreadSheet. Everything Should Be Made as Simple as Possible.

Disclaimer: You should care about Google Sheet Limitation.
This version of the Google Sheets API(v4) has a limit of 500 requests per 100 seconds per project, and 100 requests per 100 seconds per user.
Limits for reads and writes are tracked separately. There is no daily usage limit.
Read More

In this post you will know how easy and simple to import magento data to Google SpreadSheet, you can apply any kinds of data you want to import. 1 Bonus about how we create magento to Rest Api with our custom entity.

Let's start:

1. Create Module with Setup/InstallSchema.php

1/**
2     * {@inheritdoc}
3     * @SuppressWarnings(PHPMD.ExcessiveMethodLength)
4     */
5    public function install(SchemaSetupInterface $setup, ModuleContextInterface $context)
6    {
7        /**
8         * Create table 'contact_form'
9         */
10        $table = $setup->getConnection()
11            ->newTable($setup->getTable('contact_form'))
12            ->addColumn(
13                'entity_id',
14                \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
15                null,
16                ['identity' => true, 'unsigned' => true, 'nullable' => false, 'primary' => true],
17                'Entity ID'
18            )
19            ->addColumn(
20                'name',
21                \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
22                255,
23                ['nullable' => false, 'default' => ''],
24                'name'
25            )
26            ->addColumn(
27                'phone',
28                \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
29                255,
30                ['nullable' => true, 'default' => NULL],
31                'phone'
32            )
33            ->addColumn(
34                'email',
35                \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
36                255,
37                ['nullable' => true, 'default' => NULL],
38                'email'
39            )
40            ->addColumn(
41                'created_at',
42                \Magento\Framework\DB\Ddl\Table::TYPE_TIMESTAMP,
43                null,
44                ['nullable' => false, 'default' => \Magento\Framework\DB\Ddl\Table::TIMESTAMP_INIT]
45            )
46            ->setComment("Google Sheet Contact Form table");
47        $setup->getConnection()->createTable($table);
48    }

After that, complete the Model Class like Magento standard:

Model/ContactForm.php

1class ContactForm extends \Magento\Framework\Model\AbstractModel implements \Magento\Framework\DataObject\IdentityInterface
2{
3    const CACHE_TAG = 'contact_form';
4
5    protected function _construct()
6    {
7        $this->_init(\Khoaln\Contact2GoogleSheet\Model\ResourceModel\ContactForm::class);
8    }
9
10    public function getIdentities()
11    {
12        return [self::CACHE_TAG . '_' . $this->getEntityId()];
13    }
14}

Model/ResourceModel/ContactForm.php

1class ContactForm  extends \Magento\Framework\Model\ResourceModel\Db\AbstractDb
2{
3    protected function _construct()
4    {
5        $this->_init('contact_form', 'entity_id');
6    }
7}

2. Create Api Interface:

We want to use that function anywhere, no matter from EC site or client's landing page. We will expose that like a service. The next step is build Contact Form API:

Add Method save as service contract in Api/ContactFormInterface.php

1interface ContactFormInterface
2{
3    /**
4     * @param ContactFormDataInterface $contact
5     * @return mixed
6     */
7    public function save(ContactFormDataInterface $contact);
8}

Implement Class for Service Contract in Model/GoogleSheet/ContactForm.php

1class ContactForm implements ContactFormInterface
2{
3    /**
4     * @param ContactFormDataInterface $contact
5     * @return false|mixed|string
6     */
7    public function save(ContactFormDataInterface $contact)
8    {
9        return ['success' => true];
10    }
11}

Define Data Contact in Api/Data/ContactFormDataInterface.php

1interface ContactFormDataInterface
2{
3    const NAME  ='name';
4    const PHONE  ='phone';
5    const EMAIL  ='email';
6
7    /**
8     * @return string
9     */
10    public function getName();
11
12    /**
13     * @param string $name
14     * @return ContactFormDataInterface
15     */
16    public function setName($name);
17
18    /**
19     * @return string
20     */
21    public function getPhone();
22
23    /**
24     * @param string $phone
25     * @return ContactFormDataInterface
26     */
27    public function setPhone($phone);
28
29    /**
30     * @return string
31     */
32    public function getEmail();
33
34    /**
35     * @param string $email
36     * @return ContactFormDataInterface
37     */
38    public function setEmail($email);
39}

Implement Class for Data Contract in Model/GoogleSheet/ContactFormData.php

1class ContactFormData  extends AbstractSimpleObject implements ContactFormDataInterface
2{
3    public function getName()
4    {
5        return $this->_get(self::NAME);
6    }
7
8    public function setName($name)
9    {
10        return $this->setData(self::NAME, $name);
11    }
12
13    public function getPhone()
14    {
15        return $this->_get(self::PHONE);
16    }
17
18    public function setPhone($phone)
19    {
20        return $this->setData(self::PHONE, $phone);
21    }
22
23    public function getEmail()
24    {
25        return $this->_get(self::EMAIL);
26    }
27
28    public function setEmail($email)
29    {
30        return $this->setData(self::EMAIL, $email);
31    }
32}

The final step, to expose service we will define xml config:

In etc/di.xml

1<?xml version="1.0"?>
2<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
3    <preference for="Khoaln\Contact2GoogleSheet\Api\ContactFormInterface" type="Khoaln\Contact2GoogleSheet\Model\GoogleSheet\ContactForm"/>
4    <preference for="Khoaln\Contact2GoogleSheet\Api\Data\ContactFormDataInterface" type="Khoaln\Contact2GoogleSheet\Model\GoogleSheet\ContactFormData"/>
5</config>

Add route in etc/webapi.xml to available for all visitors.

1<route url="/V1/contact-form/sheet" method="POST">
2    <service class="Khoaln\Contact2GoogleSheet\Api\ContactFormInterface" method="save"/>
3    <resources>
4        <resource ref="anonymous"/>
5    </resources>
6</route>

Now on this step, we can test the input and output of our service. Open Postman or any tool can call an API.

3. Create Main Function that will import directly from our Api to Google SpreadSheet.

Preparation:

Create Account and Project on https://console.cloud.google.com/
Enable Google Drive API
Create Service Account that will execute evey update on your google sheet (Why we use service account? please read it https://developers.google.com/identity/protocols/oauth2/service-account)
Create Google spreadsheet and add editor role for service account we created above.

If you have trouble with these steps, You can see more details in here

We will use Google Drive API so that we install google api php sdk: google/apiclient v2.0.0.

Added dependency in composer.json

1{
2    "name": "khoa-le/magento-contact-2-google-sheet",
3    "description": "Import Contact Data to Google SpreadSheet",
4    "require": {
5        "php": "~7.3.22",
6        "google/apiclient": "~2.0.0"
7    },
8    "type": "magento2-module",
9    "version": "2.3.5.2",
10    "license": "MIT",
11    "autoload": {
12        "files": [
13            "registration.php"
14        ],
15        "psr-4": {
16            "Khoaln\\Contact2GoogleSheet\\": ""
17        }
18    }
19}

Now, let's start the interesting part. We will implement the function that will append data to Google SpreadSheet. Add these lines of code in Model/GoogleSheet/Api.php

1class Api
2{
3    /**
4     * @var \Google_Client $_client
5     */
6    private $_client;
7
8    /**
9     * @var \Khoaln\Contact2GoogleSheet\Helper\Data
10     */
11    private $_dataHelper;
12
13    /**
14     * ContactForm constructor.
15     * @param \Khoaln\Contact2GoogleSheet\Helper\Data $dataHelper
16     */
17    public function __construct(
18        \Khoaln\Contact2GoogleSheet\Helper\Data $dataHelper
19    )
20    {
21        $this->_dataHelper = $dataHelper;
22    }
23
24    public function append( $contact)
25    {
26        $client = $this->getClient();
27        $service = new \Google_Service_Sheets($client);
28        $range = date('Y-m', time());
29        $spreadsheetId= $this->_dataHelper->getContactFormSheetId();
30        try {
31            return $this->appendRow($service, $spreadsheetId, $range, $contact);
32        } catch (\Exception $ex) {
33            $body = new \Google_Service_Sheets_BatchUpdateSpreadsheetRequest(array(
34                'requests' => array(
35                    'addSheet' => array(
36                        'properties' => array(
37                            'title' => $range
38                        )
39                    )
40                )
41            ));
42            $service->spreadsheets->batchUpdate($spreadsheetId, $body);
43            return $this->appendRow($service, $spreadsheetId, $range, $contact);
44        }
45    }
46    private function appendRow($service, $spreadsheetId, $range, $contact)
47    {
48        // Create the value range Object
49        $valueRange = new \Google_Service_Sheets_ValueRange();
50        $valueRange->setValues(["values" => $contact]); // Add two values
51        $conf = ["valueInputOption" => "RAW"];
52        return $service->spreadsheets_values->append($spreadsheetId, $range, $valueRange, $conf);
53    }
54    private function getClient()
55    {
56        if (!$this->_client) {
57            $credential = $this->_dataHelper->getCredential();
58            $client = new \Google_Client();
59            $client->setScopes([
60                \Google_Service_Sheets::SPREADSHEETS,
61            ]);
62            //Set Service account key that config in project
63            //https://console.cloud.google.com/iam-admin/serviceaccounts/details/101147339843886042602;edit=true?previousPage=%2Fapis%2Fcredentials%3Fproject%3Dsuntory-wellness-1594265938326&project=suntory-wellness-1594265938326
64            $client->setAuthConfig($credential);
65            $this->_client = $client;
66        }
67
68        return $this->_client;
69    }
70}

We have used a configuration for 2 params on Mangento Admin, these are Service Account Credential and Google Sheet ID

On pieces of code above, you can focus on 2 functions getClient and appendRow that help you manipulate data on Google SpreadSheet easily.

Next step, we will complete the main implementation of API in save() function on Model/GoogleSheet/ContactForm.php.

1class ContactForm implements ContactFormInterface
2{
3    /**
4     * @var \Khoaln\Contact2GoogleSheet\Model\ResourceModel\ContactForm
5     */
6    private $contactForm;
7    /**
8     * @var Api
9     */
10    private $googleApi;
11    /**
12     * @var DateTime
13     */
14    private $dateTime;
15    /**
16     * @var \Khoaln\Contact2GoogleSheet\Model\ContactFormFactory
17     */
18    private $contactFormFactory;
19
20    /**
21     * ContactForm constructor.
22     * @param \Khoaln\Contact2GoogleSheet\Model\ContactFormFactory $contactFormFactory
23     * @param \Khoaln\Contact2GoogleSheet\Model\ResourceModel\ContactForm $contactForm
24     * @param Api $googleApi
25     * @param DateTime $dateTime
26     */
27    public function __construct(
28        \Khoaln\Contact2GoogleSheet\Model\ContactFormFactory $contactFormFactory,
29        \Khoaln\Contact2GoogleSheet\Model\ResourceModel\ContactForm $contactForm,
30        \Khoaln\Contact2GoogleSheet\Model\GoogleSheet\Api $googleApi,
31        DateTime $dateTime
32    )
33    {
34        $this->contactFormFactory = $contactFormFactory;
35        $this->contactForm = $contactForm;
36        $this->googleApi = $googleApi;
37        $this->dateTime = $dateTime;
38    }
39
40    /**
41     * @param ContactFormDataInterface $contact
42     * @return false|mixed|string
43     */
44    public function save(ContactFormDataInterface $contact)
45    {
46        if (!empty($contact->getName()) && (!empty($contact->getPhone()) || !empty($contact->getEmail()))) {
47            $gmt7time = date("Y-m-d H:i", strtotime('+7 hours'));
48            $createdAt = $this->dateTime->formatDate($gmt7time);
49            try {
50                $contactFormData = $this->contactFormFactory->create();
51                $contactFormData->setData("name", $contact->getName());
52                $contactFormData->setData("phone", $contact->getPhone());
53                $contactFormData->setData("email", $contact->getEmail());
54                $result = $this->contactForm->save($contactFormData);
55                if ($result) {
56                    $this->appendGoogleSheet($contact, $createdAt);
57                }
58            } catch (\Exception $exception) {
59                throw  new CouldNotSaveException(__($exception->getMessage()));
60            }
61        } else {
62            throw new InputException(__("Please fill the information"));
63        }
64        return [];
65    }
66
67    private function appendGoogleSheet($contact, $createdAt)
68    {
69        $row = [
70            $contact->getName(),
71            $contact->getPhone(),
72            $contact->getEmail(),
73            $createdAt,
74        ];
75        return $this->googleApi->append($row);
76    }
77}

The final step, We test again our Api to check It works like we imagined.

Call Rest Api /rest/V1/contact-form/sheet with POST data:

1{
2    "contact":
3    {
4        "name":"My Name",
5        "phone":"097123456",
6        "email":"[email protected]"
7    }
8}

Get 200 respone and check Database and Google Sheet.

Conclusions

The keys why we use Google SpreadSheet instead Magento Admin page:

Rollout function quickly. We can measure and know what we want to experiment.
Allow other people, partners, out source companies can access our data quickly.
The fewer people can access to Magento Admin, the more secure our Magento takes.

You can find my ready-to-use magento module on Github: https://github.com/khoa-le/magento-contact-2-google-sheet .