CODEWITHSUNDEEP
functiongoogle-sheetsautomationsku
Tom
Tom

Reputation: 159

Google Sheets - Create SKU that automates content into separate columns

I would like to create a column (SKU) in my Google sheets CSV that when data is manually input in each cell, it will automatically fill out the content in 3 other columns (Title, Colour, Size).

I have created an example CSV with the necessary columns highlighted.

Column B - Product title Column I - Colour Column K - Size Column N - SKU

Example SKU Inputs (Manually entered into column N)

L24-1-000 C-2-150

Breakdown of values assigned to each section:

L24 = Lightweight trainers S24 = Sandals

1 = Blue 2 = Red

000 = 0 150 = 1.5

Output (Automated in columns B, I and K)

'Lightweight trainers', 'Blue', '0'

'Sandals', 'Red', '1.5'

Link to CSV

https://docs.google.com/spreadsheets/d/1E6NypRcWk4kR7WGsyEzt7Uuja8dU7SaszT9lvJPpHcg/edit?usp=sharing

Upvotes: 0

Views: 2518

Answers (2)

fullfine
fullfine

Reputation: 1461

I want to propose to you an alternative solution to your problem. You can use Google Apps Script to modify and update your Spreadsheet. With this proposal, you can easily add new codes for titles and colors and different new features that are only available using Apps Script.

In this case, you need to 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

In the code that I am attaching you, there is a function called [onEdit] that is called each time that a user modifies the value of any cell in the Spreadsheet. Thanks to that trigger you can write new values in your column SKU and the others can be updated simultaneously. You just need to replace the initial function with the following code, and adapt a few things, I have added comments to help you to understand everything:

function onEdit(e) {
 
 const sheetName = 'abc' // name of your main sheet
 const col = SpreadsheetApp.getActive().getRange('N:N').getColumn() // sku column
 
// call the function that updates the columns only in the sku column has been updated
 if ( 
   e.source.getSheetName() == sheetName &&
   e.range.columnStart == col &&
   e.range.columnEnd == col)
 {
   main(sheetName) // update function
 }
}
 
 
function main(sheetName){
 // sku
 var ss = SpreadsheetApp.getActive().getSheetByName(sheetName)
 var sku = ss.getRange('N2:N').getValues().filter(String) // N: column ok sku
 var last_row = sku.length+1
  // Title
 title = [...sku]
 title.forEach(title_func)
 ss.getRange('I2:I'+last_row).setValues(title) 
 
 // Color
 color = [...sku]
 color.forEach(color_func)
 ss.getRange('K2:K'+last_row).setValues(color)
 
 // Size
 size = [...sku]
 size.forEach(size_func)
 ss.getRange('M2:M'+last_row).setNumberFormat('@').setValues(size)
}

function title_func(item, index, arr){
 const code = item[0].split('-')[0]
 var titles = {
   'L32':'Lightweight Trainers',
   'S24':'Sandals',
   'T19':'Boots',
   'T1':'Test' 
 }
 arr[index] = [titles[code]]
}

function color_func(item, index, arr){
 const code = item[0].split('-')[1]
 var colors = {
   '1':'Blue',
   '2':'Red',
   '1M':'Multicoloured',
   'C':'Cream',
   'O':'Orange',
   'T':'Test'
 }
 arr[index] = [colors[code]]
}

function size_func(item, index, arr){
 const code = item[0].split('-')[2]
 arr[index] = [code]
}

Reference

Upvotes: 1

Boris Baublys
Boris Baublys

Reputation: 1203

Please try this:

B1: =arrayformula(IF(A1:A="Handle","Title",IF(INDEX(SPLIT(N1:N, "-"),,1)="L32","Lightweight Trainers", IF(INDEX(SPLIT(N1:N, "-"),,1)="S24","Sandals", "Boots"))))

I1: =arrayformula(IF(A1:A="Handle","Option1 Value",IF(LEN(INDEX(SPLIT(N1:N, "-"),,2))>1,"Multicoloured",IF(INDEX(SPLIT(N1:N, "-"),,2)=1,"Blue",IF(INDEX(SPLIT(N1:N, "-"),,2)=2,"Red",IF(INDEX(SPLIT(N1:N, "-"),,2)="C","Cream",))))))

K1: =arrayformula(IF(A1:A="Handle","Option2 Value",MID(N1:N,LEN(N1:N) - 2,3)))

Upvotes: 1

Related Questions