Step 1) Find the row that the value in column A in sheet “DB” that matches with the value in cell “D3” in sheet “Editor”. Named the row that found row “Target Row”
Step 2) copy the value in cell “D3,F3,J3,C7:U7,C8:U8,C9:U9,C10:U10,C11:U11,E12:H12,C8:U8,J12,K12,Q12:U12 ” from sheet “Editor”
Step 3) Replace the value that gets from Step 2). in a “Target Row” by sorting values into a single row.
function copyAndPasteData() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var editorSheet = spreadsheet.getSheetByName('Editor');
var dbSheet = spreadsheet.getSheetByName('DB');
var valueToFind = editorSheet.getRange('D3').getValue();
var dbValues = dbSheet.getRange("A:A").getValues();
var matchingRowIndex = -1;
for (var i = 0; i < dbValues.length; i++) {
if (dbValues[i][0] === valueToFind) {
matchingRowIndex = i + 1; // Adding 1 to convert to 1-indexed
break;
}
}
if (matchingRowIndex === -1) {
SpreadsheetApp.getUi().alert("Value not found in DB sheet.");
return;
}
var targetRow = dbSheet.getRange(matchingRowIndex, 1, 1, dbSheet.getLastColumn());
var sourceRangesA1 = [
'D3', 'F3', 'J3', 'C7:U7', 'C8:U8', 'C9:U9', 'C10:U10', 'C11:U11', 'E12:H12',
'C8:U8', 'J12', 'K12', 'Q12:U12'
];
var sourceValues = [];
sourceRangesA1.forEach(function(rangeA1) {
var range = editorSheet.getRange(rangeA1);
sourceValues.push.apply(sourceValues, range.getValues()[0]);
});
targetRow.setValues([sourceValues]);
}
I tried to get value from DB by vlookup. edit and replace that original.
Welcome to StackOverflow! Please provide minimal reproducible example in the form of Google Sheets.
Just to clarify… is your problem getting a value for step 1? Have you tried using
indexOf
? There are plenty of examples on StackOverflow and elsewhere. Apps script won’t run built-in functions such asvlookup
.