Notes

// column values for the shopify spreadsheet
var rugNumberColumn = 1;
var descriptionColumn = 2;
var titleColumn = 3;
var bodyColumn = 4;
var styleOptionColumn = 5;
var colorOptionColumn = 6;
var sizeOptionColumn = 7;
var ourPriceColumn = 8;
var retailColumn = 9;
var savePercentageColumn = 10;
var exactSizeColumn = 11;
var constructionColumn = 12;
var shapeColumn = 13;
var materialColumn = 14;
var colorCombinationColumn = 15;
var styleColumn = 16;
var collectionColumn = 17;
var originColumn = 18;
var conditionColumn = 19;
var foundationColumn = 20;
var vendorColumn = 21;
var typeColumn = 22;
var masterSheet = "wahi-inventory-sheet";


function go() {
createTitle();
createHtmlBody();
createShopifySheet();
loadShopifySheetWithData();

}

function createHtmlBody() {

var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(masterSheet);
var data = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
// Start loop
for (var i = 2; i < data.length+1; i++) {
var topDescription = "<h1>This is a genuine hand knotted oriental rug. It is not hand tufted or a machine made rug. Our entire inventory is made of either hand-knotted or hand-woven rugs.</h1>";
var save = "<p>Save: " + ss.getRange(i, 10).getValue() + "</p>";
var exact = "<p>Exact: " + ss.getRange(i, 11).getValue() + "</p>";
var construction = "<p>Construction: " + ss.getRange(i, 12).getValue() + "</p>";
var shape = "<p>Shape: " + ss.getRange(i, 13).getValue() + "</p>";
var material = "n<p>Material: " + ss.getRange(i, 14).getValue() + "</p>";
var colorCombination = "n<p>Color Combination: " + ss.getRange(i, 15).getValue() + "</p>";
var style = "<p>Style: " + ss.getRange(i, 16).getValue() + "</p>";
var collection = "<p>Collection: " + ss.getRange(i, 17).getValue() + "</p>";
var origin = "<p>Origin: " + ss.getRange(i, 18).getValue() + "</p>";
var finalDescription =
topDescription +
save +
exact +
construction +
shape +
material +
colorCombination +
style +
collection +
origin +
"n";
// place in the correct box
ss.getRange(i, bodyColumn).setValue(finalDescription);
ss.getRange(i, bodyColumn).setFontColor("red");
} // end of the entire sheet loop for the entire sheet
} // End of createHtmlBody()

function createTitle() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(masterSheet);
var data = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
// Start loop
for (var i = 2; i < data.length+1; i++) {
var description = ss.getRange(i, 2).getValue();
var sizeBasic = ss.getRange(i, 14).getValue();
var colorCombination = ss.getRange(i, 15).getValue();
var style = ss.getRange(i, 16).getValue();
var color = ss.getRange(i, 15).getValue();
var construction = ss.getRange(i, 12).getValue();
var rugNumber = ss.getRange(i, 1).getValue();
var finalTitle =
description + " " +
sizeBasic + " " +
colorCombination + " " +
style + " " +
construction + " " +
rugNumber + " " +
"n";
// place in the correct box
ss.getRange(i, titleColumn).setValue(finalTitle);
//ss.getRange(i, titleColumn).setBackground("#7fe282");
ss.getRange(i, titleColumn).setFontColor("red");
} // end of the entire sheet loop for the entire sheet
} // End of createHtmlBody()


function createShopifySheet() {
removeShopifySheet(); // remove the old sheet
headers = [
["Handle"],
["Title"],
["Body (HTML)"],
["Vendor"],
["Type"],
["Tags"],
["Published"],
["Option1 Name"],
["Option1 Value"],
["Option2 Name"],
["Option2 Value"],
["Option3 Name"],
["Option3 Value"],
["Variant SKU"],
["Variant Grams"],
["Variant Inventory Tracker"],
["Variant Inventory Policy"],
["Variant Fulfillment Service"],
["Variant Price"],
["Variant Compare At Price"],
["Variant Requires Shipping"],
["Variant Taxable"],
["Variant Barcode"],
["Image Src"],
["Image Position"],
["Image Alt Text"],
["Gift Card"],
["SEO Title"],
["SEO Description"],
["Google Shopping / Google Product Category"],
["Google Shopping / Gender"],
["Google Shopping / Age Group"],
["Google Shopping / MPN"],
["Google Shopping / AdWords Grouping"],
["Google Shopping / AdWords Labels"],
["Google Shopping / Condition"],
["Google Shopping / Custom Product"],
["Google Shopping / Custom Label 0"],
["Google Shopping / Custom Label 1"],
["Google Shopping / Custom Label 2"],
["Google Shopping / Custom Label 3"],
["Google Shopping / Custom Label 4"],
["Variant Image"],
["Variant Weight Unit"],
["Variant Tax Code"],
["Cost per item"]
]
;


var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet(); // get active spreadsheet
var allSpreadsheets = SpreadsheetApp.getActiveSpreadsheet(); // get all the sheets

if(allSpreadsheets.getSheets()[1]) {
onOpen("Sheet is already created!");
} else {
var newSheet = activeSpreadsheet.insertSheet();
newSheet.setName("shopify-sheet"); // leave this line off if you want default names from previous line
// start header writing
for (var i=0; i < 46; i++ ) {
SpreadsheetApp.getActiveSheet().getRange(1, i+1).setValue(headers[i]);
}
// end header writing
activeSpreadsheet = SpreadsheetApp.setActiveSheet(allSpreadsheets.getSheets()[0]); // pick this one
}
} // end of createShopifySheet() function


// setValue() function
function setValue(getFromColumnValue, putToColumnValue) {
var data = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
for (var i = 2; i < data.length+1; i++) {
var all = SpreadsheetApp.getActiveSpreadsheet(); // get all sheets and place in an array called 'all'
ss = SpreadsheetApp.setActiveSheet(all.getSheets()[0]); // grab the first sheet in 'all' name it 'ss'
var valueToPasteInNewSheet = ss.getRange(i, getFromColumnValue).getValue(); // get the value from first sheet
// change sheets
ss = SpreadsheetApp.setActiveSheet(all.getSheets()[1]);
var shopifySheet = SpreadsheetApp.getActiveSheet();
// paste the value, inside the second spread sheet
shopifySheet.getRange(i, putToColumnValue).setValue(valueToPasteInNewSheet);
} // end of loop
} // end of setValue() function


function removeShopifySheet() {
var ss = SpreadsheetApp.getActiveSpreadsheet(); // get all the sheets
if(ss.getSheets()[1] != null) {
activeSpreadsheet = SpreadsheetApp.setActiveSheet(ss.getSheets()[1]); // pick this one
//onOpen('Removing :' + activeSpreadsheet);
ss.deleteActiveSheet();
} else {
//onOpen("No Sheets to delete");
}
} // end of removeShopifySheet() function


function loadShopifySheetWithData() {
var ss = SpreadsheetApp.getActiveSpreadsheet(); // get all the sheets
var data = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
activeSpreadsheet = SpreadsheetApp.setActiveSheet(ss.getSheets()[0]); // pick first spreadshet


for (var i = 2; i < data.length+1; i++) {


var ss = SpreadsheetApp.getActiveSpreadsheet(); // get all the sheets
activeSpreadsheet = SpreadsheetApp.setActiveSheet(ss.getSheets()[0]); // go to sheet 0
var description = activeSpreadsheet.getRange(i, 2).getValue();
var rugNumber = activeSpreadsheet.getRange(i, 1).getValue();
var handle =
description + "-" +
rugNumber
"n";
handle = handle.replace(/s/g, "-");
activeSpreadsheet = SpreadsheetApp.setActiveSheet(ss.getSheets()[1]); // pick first spreadshet
var shopifySheet = SpreadsheetApp.getActiveSheet();
shopifySheet.getRange(i,1).setValue(handle);

// }


// ********** Now, load the tags column **********
// for (var i = 2; i < data.length+1; i++) {
var ss = SpreadsheetApp.getActiveSpreadsheet(); // get all the sheets
activeSpreadsheet = SpreadsheetApp.setActiveSheet(ss.getSheets()[0]); // go to sheet 0

var tag1 = activeSpreadsheet.getRange(i, 5).getValue(); // from wahi-inventory-sheet
var tag2 = activeSpreadsheet.getRange(i, 6).getValue(); // from wahi-inventory-sheet
var tag3 = activeSpreadsheet.getRange(i, 7).getValue(); // from wahi-inventory-sheet
var tag4 = activeSpreadsheet.getRange(i, 22).getValue(); // from wahi-inventory-sheet
var tagList = tag1.concat(", ", tag2, ", ", tag3, ", ", tag4);

activeSpreadsheet.getRange(i,25).setValue(tagList);
// }

// ********** Now, load the published column **********
// do we want to ASK the user to set this to true or false
// for (var i = 2; i < data.length+1; i++) {
var ss = SpreadsheetApp.getActiveSpreadsheet(); // get all the sheets
activeSpreadsheet = SpreadsheetApp.setActiveSheet(ss.getSheets()[1]); // pick shopify spreadshet
var shopifySheet = SpreadsheetApp.getActiveSheet();
shopifySheet.getRange(i,7).setValue("TRUE"); // shopify-sheet
// }


// ********** Now, load the image alt text column **********
// for (var i = 2; i < data.length+1; i++) {
var ss = SpreadsheetApp.getActiveSpreadsheet(); // get all the sheets
activeSpreadsheet = SpreadsheetApp.setActiveSheet(ss.getSheets()[0]); // go to sheet 0
var imgAltText = activeSpreadsheet.getRange(i, 3).getValue(); // from wahi-inventory-sheet
activeSpreadsheet = SpreadsheetApp.setActiveSheet(ss.getSheets()[1]); // pick first spreadshet
var shopifySheet = SpreadsheetApp.getActiveSheet();
shopifySheet.getRange(i,26).setValue(imgAltText); // shopify-sheet
// }

// ********** Now, load the Variant SKU column **********
//for (var i = 2; i < data.length+1; i++) {
var ss = SpreadsheetApp.getActiveSpreadsheet(); // get all the sheets

activeSpreadsheet = SpreadsheetApp.setActiveSheet(ss.getSheets()[0]); // go to sheet 0
var firstPart = activeSpreadsheet.getRange(i, 2).getValue(); // from wahi-inventory-sheet
var secondPart = activeSpreadsheet.getRange(i, 7).getValue(); // from wahi-inventory-sheet
var tagList = tag1.concat(firstPart, "-", secondPart);
// change sheets, get prepared to post the new data inside shopify sheet
activeSpreadsheet = SpreadsheetApp.setActiveSheet(ss.getSheets()[1]); // pick shopify spreadshet
var shopifySheet = SpreadsheetApp.getActiveSheet(); // make the shopify spreadsheet active
shopifySheet.getRange(i,14).setValue(tagList); // shopify-sheet

// }


//setValue(from,to); outside function
setValue(3,2); // setting TITLE column in the shopify sheet
setValue(3,28); // setting SEO TITLE column in the shopify sheet
setValue(3,29); // setting SEO DESCRIPTION column in the shopify sheet
setValue(4,3); // setting BODY column column in the shopify sheet
setValue(21,4); // setting VENDOR column in the shopify sheet
setValue(22,5); // setting TYPE column in the shopify sheet
setValue(23,24); // setting IMAGE URL column in the shopify sheet
setValue(24,27); // setting GIFTCARD column in the shopify sheet
setValue(25,6); // setting TAGS column in the shopify sheet


} // loadShopifySheetWithData()

}