[AppSheet] A story about automatically importing Color Me Shop orders into our internal system

This time, I would like to share a story about how I imported order data from Color Me Shop into our internal system (built with AppSheet) via an API, following a certain opportunity.

The beginning

The trigger was a consultation from someone who sells bento boxes across multiple stores on Color Me Shop. Previously, when an order came in (since the store responsible for preparation is determined by the bento menu), they would distribute the order information to each store via Excel and email. However, as the number of orders increased—a happy problem to have—they began to see the limits of that operational method.

That is when I was approached through an acquaintance to see if I could do something about it using AppSheet.

The most important issue is...

The most critical issue to solve was the manual process of: receiving orders -> entering them into Excel -> creating Excel tables (which, while formula-based, required minor daily adjustments) -> sending them via email or LINE. However, I saw a path to resolving this by using AppSheet to generate PDFs or using Looker Studio to distribute them.

What I challenged this time was...

Initially, we were proceeding with the idea of importing Color Me Shop order data into AppSheet via CSV files, but I was told that there was an API available, so I attempted to import it into AppSheet using that API. As a result, I ultimately succeeded in writing GAS (Google Apps Script) to automatically import the data into AppSheet.

Twists and turns

Since I am someone who tries to do everything with no-code, I thought about using make.com instead of GAS to import the Color Me Shop data into AppSheet using their API. However, the client requested that they want to capture information without any time loss as soon as an order is received during the morning peak. If that were the case, I would have to have make.com frequently ask Color Me Shop for data, and it was obvious that this would have a major impact on the limited number of operations available in make.com. So, I reluctantly (?) turned to GAS.

After all that...

There were twists and turns, but I managed to write the GAS and succeeded in importing Color Me Shop data into AppSheet (our internal system) every minute. There were various fine details, such as not inserting data with the same ID but updating it instead, or not importing orders that haven't been paid for (or rather, there are still many things to address), but it is working somehow.

So, what are the benefits?

Centralized management of company data

The point is that by importing it into our internal system (AppSheet), if we import sales from other businesses into AppSheet in the future, we might be able to see the sales of all businesses on AppSheet, or link it with Gemini so that AI can analyze orders and provide advice.

Business efficiency through AppSheet's rich external integration features x Color Me data

AppSheet excels not only at data registration, updates, search display, and deletion, but also at integration with Slack, LINE, Google Docs, Calendar, and Drive. Therefore, by importing Color Me data, it becomes easy to, for example, write all of the day's orders into a calendar or notify LINE or Slack immediately when an order is received. Also, by accumulating data, you can perform the aggregations that AppSheet is good at, and of course, create monthly sales reports at the end of the month with the push of a button.

Data visualization

Another strength of AppSheet (though, to be honest, AppSheet's own graphing features aren't very useful 💦) is that it can seamlessly integrate with Looker Studio, a dashboard and graph creation service. This allows you to use Color Me Shop sales data to display sales trends, year-over-year comparisons, and order volumes by day of the week or time of day, which is also useful for order analysis.

In conclusion

I wrote this blog post because I felt so excited thinking about what could be achieved once I was able to retrieve Color Me Shop data using GAS.
I hope to continue helping with business efficiency, focusing on our internal system (AppSheet) combined with AI and visualization.

Thank you for reading until the end. If you found any part of this helpful, I would be happy if you could click the "Like" button.