javascript - Extract data from Shopify into a Google Sheet using Google Apps Script
I am trying to get specific product fields of a JSON object to send to a google sheet. The log tells me that 'console.log(productTitle)' is undefined and that 'Error: TypeError: products.forEach' is not a function. The structure of the Shopify object is below.
function getProducts() {
//set up url
const url = 'https://*****.myshopify.com/admin/'
const endpoint = 'products.json'
//set up parameters
const params = {
headers: {
'Content-Type': 'application/json',
'X-Shopify-Access-Token': '*****'
},
muteHttpExceptions: true
}
try {
//call the url to fetch access token
const response = UrlFetchApp.fetch(url + endpoint, params)
//parse the response and get the access token
const products = JSON.parse(response.getContentText())
console.log(response)
console.log(products)
products.forEach(product => {
const productTitle = product.products_title
console.log(productTitle)
const productId = product.products_id
const productStatus = product.products_status
})
return result
}
catch (e) {
console.log('Error: ' + e)
}
}
/* { products:
[ { id: 121345678910,
title: 'Title',
body_html: 'Body Text',
vendor: 'Vendor Name',
product_type: 'candyrack_generated',
created_at: '2021-07-18T11:04:34-05:00',
handle: 'extended-warranty-1',
updated_at: '2022-10-11T09:15:18-05:00',
published_at: '2021-07-18T11:04:34-05:00',
template_suffix: 'water-pump',
status: 'active',
published_scope: 'web',
tags: '',
admin_graphql_api_id: 'gid://shopify/Product/121345678910',
variants:
[ { product_id: 121345678910,
id: 9876543210,
title: 'Default Title',
price: '9.95',
sku: '',
position: 1,
inventory_policy: 'continue',
compare_at_price: null,
fulfillment_service: 'manual',
inventory_management: null,
option1: 'Default Title',
option2: null,
option3: null,
created_at: '2021-07-20T08:43:11-05:00',
updated_at: '2022-10-11T09:14:17-05:00',
taxable: true,
barcode: '',
grams: 0,
image_id: null,
weight: 0,
weight_unit: 'kg',
inventory_item_id: 24681012,
inventory_quantity: -708,
old_inventory_quantity: -708,
requires_shipping: false,
admin_graphql_api_id: 'gid://shopify/ProductVariant/987654' } ],
options:
[ { product_id: 121345678910,
id: 909000,
name: 'Title',
position: 1,
values: [Object] } ],
images:
[ { product_id: 121345678910,
id: 3693336,
position: 1,
created_at: '2022-04-03T08:43:29-05:00',
updated_at: '2022-04-03T08:43:32-05:00',
alt: null,
width: 1080,
height: 1080,
src: 'http://cdn.shopify.com/s/files/1/0541/4132/13/products/freereplacements.png?v=164899',
variant_ids: [],
admin_graphql_api_id: 'gid://shopify/ProductImage/369333' } ],
image:
{ product_id: 121345678910,
id: 3693336,
position: 1,
created_at: '2022-04-03T08:43:29-05:00',
updated_at: '2022-04-03T08:43:32-05:00',
alt: null,
width: 1080,
height: 1080,
src: 'http://cdn.shopify.com/s/files/1/0541/4132/13/products/freereplacements.png?v=1648993',
variant_ids: [],
admin_graphql_api_id: 'gid://shopify/ProductImage/3693336' } }
*/
I would like to pull different keys into different columns to populate rows for all products. I would also like to know how to access the Finance Reports to pull into Sheets as well. I do get a successful return of all product data 'const products = JSON.parse(response.getContentText())' , can't separate the data. Thank you.
Answer
Solution:
The log tells me that 'console.log(productTitle)' is undefined
Google Apps Script code does not run in the browser. It runs in a sandboxed environment on Google's servers, so logging works a little differently than what you might be used to in the browser. See the docs on Logging (https://developers.google.com/apps-script/guides/logging) for Apps Script.
'Error: TypeError: products.forEach' is not a function
Take a second look at the Shopify response. It's structured as:
{
products: [...]
}
So your codeproducts = JSON.parse(...) is actually referencing the response object, not theproducts array inside it. Try something like this:
const responseObj = JSON.parse(response.getContentText())
const products = responseObj.products
I would like to pull different keys into different columns to populate rows for all products. I would also like to know how to access the Finance Reports to pull into Sheets as well. I do get a successful return of all product data 'const products = JSON.parse(response.getContentText())' , can't separate the data.
You are on the right track with usingforEach to loop through the products in theproducts array (once you fix the above issue) to get individual properties of each object.
To write the data out to a Google Sheet, you'll need to explore the In short, you will need to structure the data from the Shopify response into a two-dimensional array to write the data to a sheet. Search Google for
two-dimensional array javascript if you are not familiar with this concept; two-dimensional arrays are just a way of structuring tabular data, like that in a spreadsheet. Once you have your two-dimensional array, you will write it back to the Google Sheet with
Source
Didn't find the answer?
Our community is visited by hundreds of Shopify development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.