im fairly new to this so please excuse me if the problem isn't clear enough. I’m trying to make an input sheet with a barcode, where the data gets put in the row with the right barcode. the code works fine when I put the fieldrance cells like "D4", "D5", etc

const fieldRange = ["D4","D5","D6","D7","D8"]

  const id = IDcell.getValue()
  const cellFound = DataWS.getRange ("A:A")
                        .createTextFinder(id)
                        .matchCase(true)
                        .matchEntireCell(true)
                        .findNext()

  if (!cellFound) return                     
  const row = cellFound.getRow()
  const fieldValues = fieldRange.map(f => InputWS.getRange(f).getValue())

  DataWS.getRange(row,6,1,5).setValues([fieldValues])
  fieldRange.forEach (f => InputWS.getRange(f).clearContent())
  IDcell.clearContent()
}

But whenever I want to add more cells, (cause the actual document i want to use this code for has 241 cells) I would like to change the fieldrange to ["D4:D8"]instead of adding all the cells themselves. This throws an error

Exception: The number of columns in the data does not match the number of columns in the range. The data has 1 but the range has 5.

How do I fix this error?

The full script I have now is:

function Process(){
const ss = SpreadsheetApp.getActiveSpreadsheet()

  const InputWS = ss.getSheetByName ("Input")
  const DataWS = ss.getSheetByName ("NIEUW Invoerbestand")
  const IDcell = ss.getRangeByName("Barcode")

  const fieldRange = ["D4:D8"]

  const id = IDcell.getValue()
  const cellFound = DataWS.getRange ("A:A")
                        .createTextFinder(id)
                        .matchCase(true)
                        .matchEntireCell(true)
                        .findNext()

  if (!cellFound) return                     
  const row = cellFound.getRow()
  const fieldValues = fieldRange.map(f => InputWS.getRange(f).getValue())

  DataWS.getRange(row,6,1,5).setValues([fieldValues])
  fieldRange.forEach (f => InputWS.getRange(f).clearContent())
  IDcell.clearContent()
}

CodePudding user response：

Use Range.offset(), like this:

function process() {
  const ss = SpreadsheetApp.getActive();
  const dataSheet = ss.getSheetByName('NIEUW Invoerbestand');
  const idCell = ss.getRangeByName('Barcode');
  const id = idCell.getValue();
  const cellFound = dataSheet.getRange('A:A')
    .createTextFinder(id)
    .matchCase(true)
    .matchEntireCell(true)
    .findNext()
  if (!cellFound) return;
  const fieldRange = inputSheet.getRange('Input!D4:D8');
  const fieldValues = [fieldRange.getValues().flat()];
  dataSheet
    .getRange(cellFound.getRow(), 6)
    .offset(0, 0, fieldValues.length, fieldValues[0].length)
    .setValues(fieldValues);
  fieldRange.clearContent();
  idCell.clearContent();
}