automation - Loop function to fill Crosselling template

Question

I need to create a file containing information on crosselling for my webshop.

In my example file you'll see the tab "Basic Data", this is the data available to me already. Column A contains certain products, column B shows the assigned (product)categories.

The Tab "Starting Point" shows how the final file will be structured and how the function should come into play. It would need to do the following:

1. Copy the first product from the Unique product list (D2) to A2 (already done here)

2. Paste a filterfunction into B2 (already done here) This filterfunction lists all products that belong to the same category like Product 1 except for Product 1 itself

3. Apply a numerical position tag in tens in Column C to the whole range of products related to Product 1 (in this case B2:B4), starting from 10 (..20, 30, ff) and optimally randomize it. (already done here)

4. Drag down A2, respectively paste "Product 1" into all cells below A2 until the end of the result of the filterfunction in Columns B is reached (already done here).

5. Continue the loop by pasting "Product 2" into A5, pasting the filterfunction into B5 and so on.

In "Desired Result" you can see how the end result should look like in this example. There are only 8 products, but I'd need to be able to do this for hundreds of products, that's why a function is needed.

I hope somebody is able to help me here.

question from:https://stackoverflow.com/questions/65862695/loop-function-to-fill-crosselling-template

Answer 1

You can get your desired result using Google Apps Script as I suggested.

How to use it:

• Open Apps Script clicking on Tools > Script editor and you will see the script editor. It is based on JavaScript and it allows you to create, access, and modify Google Sheets files with a service called Spreadsheet Service.
• Paste the following code and click on Run

Code

function main() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const sheetResult = ss.getSheetByName('Desired Result')
  const sheetBasic = ss.getSheetByName('Basic Data')

  // write unique products
  const val = "=ARRAYFORMULA(UNIQUE('Basic Data'!A2:A))"
  sheetResult.getRange('D2').setValue(val)

  // get basic data
  const lastColumn = sheetBasic.getRange('A:A').getValues().filter(String).length; // number of products
  const cat = sheetBasic.getRange('A2:B'+lastColumn).getValues() // product | category
  const productGroup = [] // array to store data

  // loop each product
  for (var j=0; j<cat.length; j++){
    const p1 = cat[j] 
    var k = 1
    for (var i=0; i<cat.length; i++){  
      if (cat[i][1] == p1[1] && cat[i][0] != p1[0]){
        const val = [p1[0],cat[i][0],k*10]
        k = k + 1
        productGroup.push(val)
      }
    }
  }
  var n = productGroup.length+1 
  sheetResult.getRange('A2:C'+n).setValues(productGroup)

}

Some comments

• This solution does not randomize the position value. If you need it I can help you with that, but first I want to check that this solution fits you.
• Let me know if you can obtain your desired result. Keep in mind that this solution uses the name of the sheets.

Reference

• Google Apps Script
• Spreadsheet Service
• JavaScript