Hello,
I’m trying to use Flow to export an order’s line items to a google sheet - to then be uploaded as csv file for dropship fulfillment.
Is there a way to use flow to populate the spreadsheet in the same formatting a when we export order csv files directly from the admin?
I’m currently only getting one row/line per order that seems to summarize things.
Thank you for any insight!
The Sheets action does one row at a time. We are looking to make it possible to do each lineItem as a row, but there is a way to do this now. Basically you need to separate each line item by a delimiter like “##” and then split the row in Sheets using a script.
Here is a sample Sheet that has the script, which you can copy to your account. FYI, the script is under “Apps Script” in the “Extensions” menu
https://docs.google.com/spreadsheets/d/1w9qSHe_hccBQ90dSPSVESWTFNWhdLhgMmtq6VE5ZPRA/edit?usp=sharing
Hi Paul,
Could you share the script you made in google sheets? The link is view only and does not allow me to click extensions>apps script.
Thanks
You shouldn’t need to change this except the delimiters if you use something else.
function result(range) {
//range is a list of rows
row_delimiter = "##"
col_delimiter = "::"
col_split_index = 1 //this is the column to do the split on
//get number of columns in the split
//var split1 = range[0][col_split_index].split(row_delimiter);
//var num_col_split = split1.split(col_delimiter).length;
var output = [];
//loop over the rows
for(var r=0, rLen=range.length; rHi Paul,
Thanks for the script! I got it working fine except for one little issue. I notice I am always producing one extra row at the end that is blank. I see what the issue is but not sure how to fix it.
The script I am using on flow looks like this:
{{order.name}},
{% for lineItems_item in order.lineItems %}
{{lineItems_item.name}}::{{lineItems_item.vendor}}::{{lineItems_item.currentQuantity}}::{{lineItems_item.originalUnitPriceSet.presentmentMoney.amount}}::{{lineItems_item.sku}}::{{lineItems_item.product.customProductType}}##
{% endfor %},
{{order.createdAt}},
{{order.customer.email}}
and it gives me an output similar to:
Product A::1::sku_0#product B::3::sku_2**##**
while your date was:
Product A::1::sku_0#product B::3::sku_2
-its adding a delimiter “##” at the end which makes the google sheet produce another row; what change should I make to prevent this?
To output the delimiter except on the last item you could replace ## with:
{% if forloop.last %}{% else %}##{% endif %}
You might also be able to write, but I haven’t tested:
{% unless forloop.last %}##{% endunless %}
hi @paul_n i have test the script, but it gave me an error, any idea ?
10:40:04 AM
Notice
Execution started
10:40:04 AM
Error
TypeError: Cannot read property ‘length’ of undefined
result
[email removed] Code.gs:14
You might need to get that answered by an expert in Google Sheets. It seems to suggest that something is missing in the data. But it requires lots more info to troubleshoot.
Hi Paul,
I am having a slight issue with my flow path to google sheets. I see Shopify flow creates a new column for every “,” (comma) its sees. The problem I am having is that I have a lot of my product titles with commas in them; therefore its creating unwanted columns. I used you google script which works well for separating product lines. Is there a way to get shopify to ignore making new columns for each “,” (comma ) it sees.
For Example: The product title could be: *ALT ALT75085401- Handshower For 2,3,4 Pce Tub Filler And Floormount, and the commas that are in the products name are automatically registering within the app as a break, and it is separating the information
You need to find and replace commas like:
{{ order.title | replace:',', '' }}
Where would I place that code?
You would put the | replace part after any field name you are using that
might have commas
i have test this but always give me a ## output at the end, what i’m a doing wrong ?
{{order.name}},
{% for lineItems_item in order.lineItems %}{% if lineItems_item.product.totalInventory <= 0 %}{{lineItems_item.name}}::{{lineItems_item.currentQuantity}}{% if forloop.last %}{% else %}##{% endif %}{% endif %}
{% endfor %},
shopify,{{order.customer.firstName}} {{order.customer.lastName}}
The script is incomplete it seems; you need to first receive the trigger from Sheets when a row is added. I think it should be this - it works on manual row additions, but it’s not working from Sheets yet for me:
function onEdit(e) {
const outputSheetName = "Copy of Transactions"
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const outputSheet = spreadsheet.getSheetByName( outputSheetName );
const range = e.range
const output = result(range.getValues())
// do something with output
}
(onEdit is a reserved trigger / callback in google App Scripts)
How did you pass the argument to:
result( range )
since the script he pasted just opens with that? I got it all working with manual Sheet edits, but Flow is connected and it’s still not triggering this function
If you are looking for support on how to change a script in Google Sheets, you might have better luck on Stack Overflow or over in Google Sheets support forums.
Hi Paul, I have been using your google script for a while, and for the most part it has worked quite well. However, I often run into this issue “Loading Error” where the function is just stuck. This pretty much happens daily and the only way I have been able to fix it is to manually delete the function for the cell and re-paste it. It’s not really practical and not having the function work really causes me issues. Any ideas on how to fix this?
Seems like a Sheets bug and we are not Sheets experts. I’ve personally never seen that issue. I’d recommend trying Stack Overflow or something like that.
Hey Manny, what’s the formula you have in that cell? I have some experience with sheets and would like to help if I can. I may end up building a slightly different method for extracting the data and getting a list of line items.
