I need additional assistance with my project. The endgame goal is to have a user select cells in a row and then be able to use a button to upload that data into another sheet. When uploading I want to match on a specific cell and fill in specific blank cells on that row. If it doesn't find a match I need it to create a new entry.
The idea is there are multiple sheets that the team uses, each sheet has the same categories but only certain cells are filled out in each sheet. So we want to combine the data all in one sheet so you don't need to go looking at a bunch of different sheets to get all the data. For example, product ID 123456 is present in the master so when a user uploads data with that product ID it would add the information such as weight, height, etc, to the appropriate cells in that row. If the user uploads Product ID 654321 and it's not present in the master then it would create a new line with the data selected and put into the appropriate cells.
So right now I have it uploading everything to a master sheet without checking any product ID. Every time it creates a new entry. I need it to check a specific cell data and if it's present in the master then do not add a the data in a new line, instead, add the data to that row in the appropriate cell.
so far it's pretty simple:
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 sheet
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
});
};
Here is a picture of the the sheets: Uploading Sheet Master Sheet
In this there is only one item to upload with product code of 1 So what I need is for it to search the master for a matching product code and add the information if present and if not create a new entry with that product code. I'm unsure how to go about this at this time..
