How can I use Flow to add order rows to a Google sheet?

[Msimons]

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!

[paul_n]

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

[Manny98]

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

[paul_n]

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; r<rLen; r++) {            
    //loop over the column to be split to get the new number of rows
    var split_rows = range[r][col_split_index].split(row_delimiter);      //get the column to split on
    for(var sr = 0, srLen=split_rows.length; sr<srLen; sr++) {
      var new_row = []; 
      //loop over each column
      for(var c=0, cLen=range[0].length; c<cLen; c++) {
        if(c != col_split_index) {                          //if it's the column that was split, just save that split text to the column
          new_row.push(range[r][c]);                        //get the field for this row/column
        }
        else {
          //loop over the columns in the split
          var split_cols = split_rows[sr].split(col_delimiter); 
          for(var sc=0, scLen=split_cols.length; sc<scLen; sc++) {
            new_row.push(split_cols[sc]);
          }
        }
      }
      output.push(new_row);                                //add this row to the list 
    }
  }
  return output
}

/*
  range looks like:
  [r1[col1, col2, col3a::val3a##col3b::val3b, col4], r2[col21, col22, col23a::val23a##col23b::val23b, col24]]
  which should be transformed to look like:
  col1, col2, col3a, val3a, col4
  col1, col2, col3a, val3b, col4  
  col21, col22, col23a, val23a, col24
  col21, col22, col23a, val23b, col24  
*/

[Manny98]

Hi 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?

[paul_n]

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 %}

[precision_efi]

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}}

[okonno]

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

[paul_n]

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.

[precision_efi]

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

@ Code.gs:14

[paul_n]

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. 

[okonno]

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
}

[okonno]

(onEdit is a reserved trigger / callback in google App Scripts)

[Manny98]

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?

[paul_n]

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.

[MasterJ-LIF]

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.

[Manny98]

Hi!

The code I used is the same from above:

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; r<rLen; r++) { //loop over the column to be split to get the new number of rows var split_rows = range[r][col_split_index].split(row_delimiter); //get the column to split on for(var sr = 0, srLen=split_rows.length; sr<srLen; sr++) { var new_row = []; //loop over each column for(var c=0, cLen=range[0].length; c<cLen; c++) { if(c != col_split_index) { //if it's the column that was split, just save that split text to the column new_row.push(range[r][c]); //get the field for this row/column } else { //loop over the columns in the split var split_cols = split_rows[sr].split(col_delimiter); for(var sc=0, scLen=split_cols.length; sc<scLen; sc++) { new_row.push(split_cols[sc]); } } } output.push(new_row); //add this row to the list } } return output } /* range looks like: [r1[col1, col2, col3a::val3a##col3b::val3b, col4], r2[col21, col22, col23a::val23a##col23b::val23b, col24]] which should be transformed to look like: col1, col2, col3a, val3a, col4 col1, col2, col3a, val3b, col4 col21, col22, col23a, val23a, col24 col21, col22, col23a, val23b, col24 */

I still run into loading errors occasionally. It appears to be triggered when data is added to the spreadsheet from Shopify Flow.

[Manny98]

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

[paul_n]

You need to find and replace commas like:

{{ order.title | replace:',', '' }}

[Manny98]

Where would I place that code?

[paul_n]

You would put the | replace part after any field name you are using that
might have commas

[LukeRotherfield]

Hey Paul

Is it possible to escape the comma?  I want to insert a formulae into a field from Shopify flow but I need commas in that formulae.  I have tried wrapping the whole string in double and single quotes but neither seems to work.  This is what I am trying to insert:

=IF(("{{order.createdAt | date: "%Y-%m-%d %H:%M:%S"}}"-"{{order.customer.emailMarketingConsent.consentUpdatedAt | date: "%Y-%m-%d %H:%M:%S"}}")<TIME(0, 1, 0),"YES","NO")

Cheers

Luke 

[paul_n]

I tested this...

Add a backslash to commas you don't want to send as new columns.

[JJ9519]

Hello, 
Would someone please be able to paste the full script after these changes were made? Would like to copy it all over to my fulfilment sheet but I am a bit lost with what the final result was!

[paul_n]

There are multiple scripts so it's not clear which you are asking for. 

I'll add...now that "For Each" loops are available, you can usually loop over line items using that action and call the Sheets action for each line item without code. 

[djaguero]

Can you show me, how can I connect Shopify Flow with Google Sheet?

How do I send data to a spreadsheet by Shopify flow?

Thanks