Story of an integration between Google Drive and Shopify

Hey there! Some time ago, I embarked on this personal project — I needed to establish an online store. I reckoned Shopify was the ideal platform to keep things running smoothly, allowing me to dedicate more time to the business itself. However, as time went on, I realized certain tasks on Shopify were consuming far too much of my time. Let me break it down for you.

You see, the products I was selling were unique, which meant I couldn’t simply create one product with, let’s say, 10 units. No, I had to manually set up 10 different products, one by one, including titles, descriptions, images, and so on. My initial workaround involved creating a template for each product with basic information and then cloning it for each item I wanted to sell. Yet, there were still aspects that required manual adjustment — such as adding images and additional details.

That’s when I had a eureka moment and decided to develop an app that could integrate our product information repository with Shopify. The objective? To automate the entire process of product creation and updates, eliminating those pesky human errors. Since all our master data was stored on Google Sheets, the plan was to seamlessly pull, modify, and transmit that data to Shopify.

Sounds straightforward, doesn’t it? Well, it wasn’t exactly a walk in the park, but hey, it wasn’t rocket science either. Let me guide you through it.

My blueprint was:

1_ Get all the credentials and privileges required to have this integrations up and running, Shopify, Google, GitHub.

2_ Test Shopify public APIs, to confirm understand the data requirements and validate the authentication.

3_ Create an app to get Shopify product data.

4_ Create an app’s method to create a list of product.

5_ Create an app’s method to update a list of product.

6_ Modify the app’s method to create list of products using Google Sheets data.

7_ Modify the app’s method to update list of products using Google Sheets data.

8_ Modify the app’s method to update products to uploading product images from Google Drive.

Important points to understand why it was done like this:

This is a personal project, I just need it a easy way to achieve my needs. So, the app endpoints are basically triggers, all the required information is within the app.

server.port=8090

shopify.api.access.token=YOUR_SHOPIFY_TOKEN_STARTS_WITH_shpat_****
shopify.api.header=X-Shopify-Access-Token
shopify.api.url.product=https://YOUR_SHOP.myshopify.com/admin/api/2023-07/products.json?ids=%s
shopify.api.url.product.creation=https://YOUR_SHOP.myshopify.com/admin/api/2023-07/products.json
shopify.api.url.product.update=https://YOUR_SHOP.myshopify.com/admin/api/2023-10/products/%s.json
shopify.api.url.collection.add.product=https://YOUR_SHOP.myshopify.com/admin/api/2023-10/collects.json

google.spreadsheet.id=YOUR_GOOGLE_SHEET_FILE_ID
google.drive.images.folder.id=YOUR_GOOGLE_DRIVE_IMAGES_FOLDER_ID
google.application.name=YOUR_GOOGLE_APPLICATION_NAME

No unit tests were created, which is something I’ll need to address in upcoming versions. I’ll delve into the future plans for this project shortly. Currently, the Google Sheets APIs don’t retrieve filtered data. Consequently, I must fetch all the information and filter it within the application. Hopefully, Google will enable this functionality in the future.

So let me show how this personal project ended up.

First, I established a new project on Google Cloud, following Google’s comprehensive guide — without incurring any costs. Next, I acquired a Shopify Partner Account, also free of charge. Finally, I obtained the crucial Shopify access token, a pivotal element in our integration process. Frankly, there’s no need to detail these steps here since Google and Shopify have already provided excellent guidance.

It took some time to organize everything, but once it was all set up, I hardly needed to make any adjustments. Before immersing myself in the integration process, I deemed it wise to proceed methodically. I began by test-driving Shopify APIs using Postman, ensuring everything was robust before proceeding further.

Just a GET call to Shopify API will be enough to test it:

https://YOUR_SHOP.myshopify.com/admin/api/2023-07/products.json?ids=YOUR_PRODUCT_ID

Be aware you need to add a security header:

Key: X-Shopify-Access-Token

Value: Your Shopify Access Token

Once satisfied with the groundwork, I progressed to requesting data products through our app, already established on Shopify — an initial glimpse into the capabilities of our integration.

Product Controller:

@RestController
@RequestMapping("/product")
public class ProductController
{
    @Autowired
    Product productInterface;

    @GetMapping("/get")
    public ShopifyProductResponse getProduct(String productId)
    {
        return productInterface.getProduct(productId);
    }

And the Interface implementation:

@Service
public class ProductImpl implements Product
{
@Autowired
ShopifyEntity httpEntityInterface;

@Value("${shopify.api.url.product}")
private String apiProductUrl;

@Override
public ShopifyProductResponse getProduct(String productId)
{
    RestTemplate restTemplate = new RestTemplate();
    ResponseEntity<ShopifyProductResponse> response = restTemplate.exchange(String.format(apiProductUrl, productId), HttpMethod.GET, httpEntityInterface.getShopifyEntity(), ShopifyProductResponse.class);
    return response.getBody();
}
....

Now, onto the exciting part. As creating products through a REST API had its limitations, only allowing the addition of a minimum number of fields, I opted to keep it straightforward and provide only essential information for now. Some details were even hardcoded because, let’s admit it, there’s no point in fetching real-time data at this stage. We’ll reserve that for when we’re updating the products later on.

 @Override
    public List<ShopifyProductResponse> createProducts()
    {
        List<List<Object>> fileContent = googleSheets.getSheetData(SHEET_PIECES + "A2:D");
        List<List<Object>> fileContentFiltered;

        if (fileContent == null || fileContent.isEmpty())
        {
            System.out.println("No data found.");
            return null;
        } else
        {
            fileContentFiltered = filterProductListToCreate(fileContent);
        }

        List<ShopifyProductCreation> productToCreateList = getProductListToBeCreated(fileContentFiltered);
        List<ShopifyProductResponse> productCreatedList = new ArrayList<>();
        for (ShopifyProductCreation product : productToCreateList)
        {
            productCreatedList.add(createOnlineProduct(product));
        }

        addProductIdAndLockItInSpreadsheet(productCreatedList, fileContentFiltered);

        return productCreatedList;
    }

On my implementation, “Pieces” it’s the Google sheet tab where I keep the required data. So getting data from Pieces!A2:D allows me to filter the list of product’s SKU I need to work with.

Basically , I need all the SKUs with columns C and D empty. As the column C is the Shopify ID (Shopify retrieves this ID once the product is created). The column D is a flag, where “Y” means the product has been created and published and “L” means the product is locked, product has been created but shouldn’t be published.

Once the products were created on Shopify the Google Drive data will be updated, setting the Shopify product ID and also the flag to “Y”:

Fantastic! We’ve successfully created a lineup of products on Shopify using our app, utilizing the information sourced from Google Sheets. However, there’s more work to be done! It’s time to roll up our sleeves and delve into the details. We need to refine these products, incorporating tailored details based on their respective types. And, of course, let’s not forget the crucial step of adding the corresponding images to each product.

Product Controller:

@GetMapping("/create")
public List<ShopifyProductResponse> createProduct()
{
 return productInterface.createProducts();
}

and a part of the interface implementation:

@Override
public List<ShopifyProductResponse> updateProducts()
{
 List<List<Object>> fileContent = googleSheets.getSheetData(SHEET_PIECES + "A2:" + SPREADSHEET_PUBLISHED_COLUMN);
 List<List<Object>> fileContentFiltered = filterProductListToUpdate(fileContent);
 List<ShopifyUpdateProductRequest> productListToBeUpdated = getProductListToBeUpdated(fileContentFiltered);
 List<ShopifyProductResponse> updatedProducts = new ArrayList<>();
 for (int i = 0; i < productListToBeUpdated.size(); i++)
 {
  ShopifyProductResponse updatedProduct = updateOnlineProduct(String.format(apiProductsUpdateUrl, productListToBeUpdated.get(i).getProduct().getId()), productListToBeUpdated.get(i));
  updatedProducts.add(updatedProduct);
  collectionInterface.addProductToCollection(productListToBeUpdated.get(i).getProduct().getId(), productListToBeUpdated.get(i).getProduct().getVariants().get(0).getSku().split("-")[0]);
  tagProductAsPublished(fileContentFiltered.get(i));
 }

 return updatedProducts;
}

I’m sure you’ve noticed that we’re adding the product to a collection. This requires making a call to a different Shopify API to add the new product to an existing collection.

@Override
public boolean addProductToCollection(String productId, String sku)
{
    List<List<Object>> fileContentFields = googleSheets.getSheetData("Fields!A2:L");
    List<Object> fileContentFieldsFiltered = fileContentFields.stream()
            .filter(row -> row.get(0).equals(sku))
            .flatMap(List::stream)
            .collect(Collectors.toList());
    String collectionName = COLLECTIONS.get(fileContentFieldsFiltered.get(11).toString());
    ShopifyCollectRequest collectRequest = new ShopifyCollectRequest();
    collectRequest.setCollect(new ShopifyCollectRequest.Collect(productId, collectionName));
    ResponseEntity<Object> response = new RestTemplate()
            .exchange(apiCollectionAddProductUrl, HttpMethod.POST, httpEntityInterface.getShopifyEntityWithBody(collectRequest), Object.class);

    return response.getStatusCode().equals(HttpStatus.CREATED);
}

To avoid overwhelming you with too much detail at this juncture, I won’t include the entire code here. Instead, I’ll provide enough to illustrate how the app functions. However, you can find the complete project at the end of the article.

Additionally, we need to incorporate product images. These images are stored in a specific folder on Google Drive. The naming convention for the images is:

productNumber + underscore + imageOrder

So, for example the image 41_1 it’s the first image for the product #41

private ArrayList<ShopifyUpdateProductRequest.Image> getProductImages(String productUID, FileList imagesData, List<Object> productAltTexts)
{
 List<File> filteredImagesData = imagesData.getFiles().stream()
 .filter(file -> file.getName().split("-")[0].equals(productUID))
 .collect(Collectors.toList());
 String[] imageAltText = productAltTexts.get(1).toString().split("\n");
 ArrayList<ShopifyUpdateProductRequest.Image> productImages = new ArrayList<>();
 for (File image : filteredImagesData)
 {
  ShopifyUpdateProductRequest.Image productImage = new ShopifyUpdateProductRequest.Image();
  productImage.setSrc(image.getWebContentLink());
  int imagePosition = getImagePosition(image);
  productImage.setPosition(imagePosition);
  if (imagePosition <= imageAltText.length)
  {
   productImage.setAlt(imageAltText[imagePosition - 1]);
  } else
  {
   productImage.setAlt(imageAltText[imageAltText.length - 1]);
   System.out.println(ANSI_YELLOW + "Product #" + productUID + " contains a default alt text, because the original image position on the image's name it's not valid to get the correct alt text" + ANSI_RESET);
  }
  productImages.add(productImage);
 }

 return productImages;
}

Final products created and edit on Shopify:

All the Google Authentication tasks are done in: GoogleAuthorizeImpl.java

Some tips about how I keep all the data on Google Sheets (data modeling):

• Pieces sheet: contains the product ID, SKU, Shopify ID, published flag.
• ImagaData sheet: contains the relationship between Product Collections and images ALT texts.
• Fields sheet: contains the relationship between SKU and its attributes, like weight, title, tags, description, collection, etc.

Google APIs tip:

• Delete the token after change the Google application privileges, as the app won’t get a new token if the current one still valid.
• Don’t forget to configure your endpoint to get the token (http://localhost:8888/Callback) . Same port set on GoogleAuthorizeImpl.java

LocalServerReceiver receiver = new LocalServerReceiver.Builder().setPort(8888).build();

I hope this example will help you. Please check the project here : gitHub

My next steps for this project include:

1. Migrating all the data from Google Sheets to a database.
2. Uploading the application to the cloud, possibly AWS.

See you in the next article :)