Need to create app script copy and replace button for google sheets

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.

  • 1

    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 as vlookup.

    – 

Leave a Comment