I require help with my project. The ultimate objective is for a user to choose cells in a row and then utilize a button to transfer that data to another sheet. Upon uploading, I aim to match a specific cell and populate particular empty cells on that row. In case of no match, it should generate a new entry.
The concept revolves around multiple sheets utilized by the team, each containing similar categories with some cells filled out selectively. The goal is to amalgamate all the data into one sheet, eliminating the need to refer to various sheets for information retrieval. For instance, if product ID 123456 exists in the master sheet, uploading data with this product ID should append details such as weight, height, etc., to the corresponding cells on that row. On the other hand, when a user uploads Product ID 654321 not present in the master, a new line accommodating the selected data should be created, filling the appropriate cells accordingly.
In its current state, the script uploads everything to a master sheet without cross-checking the product ID. It keeps generating new entries every time the script runs. What I seek is to verify a specific cell's data - if found in the master, avoid adding the data in a new line; instead, fill the respective cells within that row.
As of now, the script is relatively straightforward:
function uploadData(){
var css = SpreadsheetApp.getActiveSpreadsheet(); //set up current spreadsheet
var csheet = css.getSheetByName("Sheet1") //set up current sheet
var cdata = csheet.getActiveRange().getValues(); //retrieve selected data values
var sRows = csheet.getActiveRange().getLastColumn(); //extract the value of the last column selected
var mss = SpreadsheetApp.openById('1N5Orl2fQvmFmK63_y78V2k7jzBUYOjDxhixMSOCU7jI'); //access mastersheet for data addition
var msheet = mss.getSheets()[0]; //fetch the sheet
var mfindnextrow = msheet.getRange('A:A').getValues(); //obtain the values
//this following code allows insertion of new data on an empty row
var maxIndex = mfindnextrow.reduce(function(maxIndex, row, index) {
return row[0] === "" ? maxIndex : index;
}, 0);
//begin iterating through the row
cdata.forEach(function(row){
msheet.setActiveRange(msheet.getRange(maxIndex + 2, 1, 1, sRows)).setValues(cdata); //insert the data into the mastersheet
});
};
Referencing the Uploading sheet: uploadsheet This sheet contains only one item with product code 1 I am seeking a solution where the system searches the master for a matching product code, appends the information if available, and creates a new entry with the specified product code if necessary.