Inventory numbers on Google Sheets based on Shopify order
Update inventory numbers on Google Sheets based on Shopify orders
To automatically update inventory numbers on Google Sheets based on Shopify orders, you can use a combination of tools like Zapier or a custom solution using Google Apps Script and Shopify's API. Here’s how you can do it using both methods:
1. Using Zapier
Zapier allows you to connect Shopify with Google Sheets without needing to write code.
Steps:
Create a Google Sheet for Inventory:
Set up a Google Sheet where you track inventory numbers. Ensure each product has a unique identifier (e.g., SKU, Product Name).
Create a Zap in Zapier:
Trigger: Set the trigger to "New Order in Shopify."
Action: Set the action to "Lookup Spreadsheet Row in Google Sheets." This will help you find the row that corresponds to the product ordered.
Action: Add another action, "Update Spreadsheet Row in Google Sheets." This will update the inventory count by subtracting the quantity of the product sold from the current inventory.
Mapping Fields:
Map the Shopify order details (e.g., product ID, quantity) to the appropriate fields in your Google Sheet.
Use Zapier's built-in functions to calculate the new inventory number.
Test the Zap: Place a test order on Shopify and see if it updates the inventory in your Google Sheet correctly.
Turn on the Zap: Once the test is successful, activate the Zap to keep your inventory numbers updated automatically.
2. Using Google Apps Script and Shopify API
If you prefer more control or need additional customization, you can write a Google Apps Script that pulls data from Shopify and updates your Google Sheet.
Steps:
Set Up Google Sheets:
As with the Zapier approach, ensure you have a Google Sheet that tracks your inventory, with unique identifiers for each product.
Obtain Shopify API Credentials:
Go to your Shopify Admin -> Apps -> Manage private apps -> Create new private app.
Get the API key, password, and shared secret for accessing Shopify's API.
Write the Google Apps Script:
Open your Google Sheet.
Go to Extensions -> Apps Script and start writing a script to fetch orders from Shopify and update the inventory.
Here’s an example script:
function updateInventoryFromShopify() {
const shopifyApiUrl = 'https://your-shop-name.myshopify.com/admin/api/2023-07/orders.json?status=any';
const shopifyApiKey = 'your-api-key';
const shopifyPassword = 'your-password';
const options = {
method: 'get',
headers: {
'Authorization': 'Basic ' + Utilities.base64Encode(shopifyApiKey + ':' + shopifyPassword)
}
};
// Fetch orders from Shopify
const response = UrlFetchApp.fetch(shopifyApiUrl, options);
const orders = JSON.parse(response.getContentText()).orders;
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Inventory'); // Replace 'Inventory' with your sheet name
const data = sheet.getDataRange().getValues(); // Get all data in the sheet
orders.forEach(order => {
order.line_items.forEach(item => {
const productName = item.name;
const quantitySold = item.quantity;
// Loop through the sheet to find the product
for (let i = 1; i < data.length; i++) { // Start from 1 to skip the header row
if (data[i][0] === productName) { // Assuming the first column has the product name
const currentInventory = data[i][1]; // Assuming the second column has the inventory count
const newInventory = currentInventory - quantitySold;
sheet.getRange(i + 1, 2).setValue(newInventory); // Update the inventory in the second column
break;
}
}
});
});
}
// Optional: Set a trigger to run the script automatically at intervals
function setupTrigger() {
ScriptApp.newTrigger('updateInventoryFromShopify')
.timeBased()
.everyHour() // Adjust the timing as needed
.create();
}
Run the Script:
Test the script by running it manually.
If it works correctly, it will subtract the quantity sold from the current inventory in your Google Sheet.
Set Up an Automatic Trigger:
Use the setupTrigger function to set up a time-based trigger so the script runs at regular intervals (e.g., every hour) to keep the inventory updated.
Conclusion
Zapier: Easier to set up, no coding required, but limited customization.
Google Apps Script: Requires some coding, but offers full control and customization.
Using Make.com (formerly Integromat) to update inventory numbers on Google Sheets based on Shopify orders involves setting up a scenario that connects Shopify and Google Sheets. Below are the steps:
Steps to Update Inventory on Google Sheets Using Make.com
Create a Google Sheet for Inventory:
Set up a Google Sheet with columns for product identifiers (e.g., SKU, Product Name), current inventory, and any other relevant information.
Log into Make.com and Create a New Scenario:
Go to Make.com and log in.
Click on Create a new scenario.
Add a Shopify Module:
In the scenario editor, click the plus (+) icon and search for "Shopify".
Choose the "Watch Orders" module.
Connect your Shopify account to Make.com using your Shopify store's API credentials.
Configure the module to watch for new orders. You can filter the orders based on specific criteria, such as order status.
Add a Google Sheets Module:
After the Shopify module, click the plus (+) icon and search for "Google Sheets".
Choose the "Search Rows" module.
Connect your Google account and select the spreadsheet and sheet where your inventory data is stored.
Set up the search to find the row corresponding to the product in the Shopify order using a unique identifier like SKU or Product Name.
Add a Google Sheets Update Module:
After the "Search Rows" module, add another Google Sheets module.
Choose the "Update a Row" module.
Use the result from the "Search Rows" module to identify which row to update.
Configure it to subtract the quantity sold from the current inventory count.
Map Data Between Modules:
Map the data from Shopify (e.g., product name, quantity) to the Google Sheets fields in the "Search Rows" and "Update a Row" modules.
Ensure that the inventory number is updated correctly by performing calculations directly in Make.com if needed (e.g., subtracting the quantity sold from the current inventory).
Test the Scenario:
Run a test to ensure everything is working as expected. Place a test order on Shopify, and check if the inventory in your Google Sheets updates correctly.
Schedule the Scenario:
Set the scenario to run automatically by clicking the clock icon and scheduling it to run at intervals that suit your needs (e.g., every 15 minutes).
Activate the Scenario:
Once you’ve tested everything and it’s working correctly, activate the scenario. It will now automatically update your Google Sheets inventory based on new Shopify orders.
Conclusion
Using Make.com offers flexibility and control over your workflows without needing to write code. You can easily connect Shopify and Google Sheets to update your inventory automatically, saving time and reducing errors.