Disclaimer: I am just new in coding and I just want to automate things up in my work.

I want to copy a data from 'Data Entry' sheet to another sheet specified by the cell in 'Data Entry' sheet.

here is the code that I am using:

function DataEntry() {
  let spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  let sourceSheet = spreadSheet.getSheetByName('Data Entry');

  let sourceRange = sourceSheet.getDataRange();
  let sourceValues = sourceRange.getValues();

  let rowCount = sourceValues.length();
  let columnCount = sourceValues[0].length();

  let targetSheet = spreadSheet.getSheetByName('6');
  let targetRange = targetSheet.getRange(2,1,rowCount,columnCount);

  targetRange.setValues(sourceValues);
}

In the source sheet, I want to copy the data starting from row 3, column L. (the number of rows varies every data batch)

Then in the target sheet, which will be specified by a cell in source sheet (cell T2). The pasting should start at row 2, column A.

CodePudding user response：

function DataEntry() {
  let spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  let sourceSheet = spreadSheet.getSheetByName('Data Entry');

  let sourceRange = sourceSheet.getDataRange();
  let sourceValues = sourceRange.getValues();
  let sheetName = sourceValues[1][19];
  sourceValues = sourceValues.slice(2).map(row => row.slice(11));

  let rowCount = sourceValues.length;
  let columnCount = sourceValues[0].length;

  let targetSheet = spreadSheet.getSheetByName(sheetName);
  let targetRange = targetSheet.getRange(2,1,rowCount,columnCount);

  targetRange.setValues(sourceValues);
}

Explanation:

• Since getValues() returns a 2D array, in order to get the value from a certain cell you just have to use the appropriate array indexes to access the desired element (in this case, 1 and 19, corresponding to T2).
• Use map and slice to retrieve a subset of the range values. You could also retrieve the corresponding range directly (e.g. sourceSheet.getRange("L3:L").getValues().flat().filter(String)), but in this way you are minimizing the amount of calls to the spreasdheet service, which is a good practice.