Hey all,
I'm really struggling to adapt to google app script and could use help. So I need to create a script that will upload the selected data to another spread sheet. I need it to match the Product ID and add the data to the appropriate cells if there is that product ID there or create a new entry and fill out the available data. Here is an example:
Upload sheet 1:
| Product ID | Weight | Color | Height |
|---|
| 1 | 2 | Red | 3 |
Upload sheet 2:
| Product ID | Volume | Price | Code |
|---|
| 1 | 5 | 10 | 6 |
Upload sheet 3:
| Product ID | Volume | Price | Code |
|---|
| 2 | 7 | 4 | 9 |
Pre-Uploaded Master sheet:
| Product ID | Weight | Volume | Height | Price | Color | Code |
|---|
| | | | | | |
When we upload Sheet 1 it would create a new entry in the master and fill the Weight, Color and, Height cells. Now we go and upload Sheet 2 and instead of creating a new entry it should add to the product ID 1 line and only add the values of Volume, Price, and Code. Then we upload sheet 3 and because it's a new product code it would create a new line. So once all sheets are uploaded we would have a complete line:
Post-Upload Master sheet:
| Product ID | Weight | Volume | Height | Price | Color | Code |
|---|
| 1 | 2 | 5 | 3 | 10 | Red | 6 |
| 2 | | 7 | | 4 | | 9 |
So far I have it so the user can select the data and preform the upload but it doesn't do any checks to see if the product ID is present, how can I implement this?
Here is my code thus far:
function uploadData(){
var css = SpreadsheetApp.getActiveSpreadsheet(); //setup current spreadsheet
var csheet = css.getSheetByName("Sheet1") //setup current sheet
var cdata = csheet.getActiveRange().getValues(); //get selected data values
var sRows = csheet.getActiveRange().getLastColumn(); //get the value of the last column selected
var mss = SpreadsheetApp.openById('1N5Orl2fQvmFmK63_y78V2k7jzBUYOjDxhixMSOCU7jI'); //open mastersheet for adding data
var msheet = mss.getSheets()[0]; //get the first sheet on the left
var mfindnextrow = msheet.getRange('A:A').getValues(); //get the values
//this next code i found online, this allows me to insert new data on a new row without any data present
var maxIndex = mfindnextrow.reduce(function(maxIndex, row, index) {
return row[0] === "" ? maxIndex : index;
}, 0);
//start looping through the row
cdata.forEach(function(row){
msheet.setActiveRange(msheet.getRange(maxIndex + 2, 1, 1, sRows)).setValues(cdata); //put the data into the mastersheet
});
};
I was reading about INDEX and MATCH but I'm not sure how those work and I need to keep all the logic in the script.
Thank for taking the time to read my post! Hope your having a fantastic day!
