I am copying source sheet data to target sheet by using following code (Thanks to Iamblichus).

How to append the target sheet data with "=GOOGLEFINANCE(A1,price)" for all rows respectively. I want to copy formula not value of the formula, so that I can get live value.

Column A of target sheet has stock codes.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sourceSheet = ss.getSheetByName("Source");
const targetSheet = ss.getSheetByName("Target");
const startRow = 2; 
const CHECK_COLUMN = 1; // Column A

function appendToRecords() {
  const numRows = sourceSheet.getLastRow() - 1; // Number of rows to process
  const numCols = sourceSheet.getLastColumn();
  const lastTargetRow = targetSheet.getLastRow();
  const dataRange = sourceSheet.getRange(startRow, 1, numRows, sourceSheet.getLastColumn()); // Fetch the range of cells being used
  const sourceData = dataRange.getValues(); // Fetch values for each row in the Range.
  let targetData = [];
  // if (lastTargetRow > 1) targetData = targetSheet.getRange(startRow, 1, lastTargetRow-startRow 1, numCols).getValues();  // For heading in TSheet
  if (lastTargetRow > 0) targetData = targetSheet.getRange(1, 1, lastTargetRow, numCols).getValues();   // For no heading in Tsheet 
  const newData = sourceData.filter(sourceRow => {
    const columnA = sourceRow[CHECK_COLUMN-1];
    return !targetData.some(targetRow => targetRow[CHECK_COLUMN-1] === columnA);
  });
  if (newData.length) {
    targetSheet.getRange(lastTargetRow 1, 1, newData.length, newData[0].length).setValues(newData);
  }
}

CodePudding user response：

Not sure where exactly you want to write the formulas, but, in case you want to add them to the first empty column (I), you can just use map to append the formula to each row:

  const newData = sourceData.filter(sourceRow => {
    const columnA = sourceRow[CHECK_COLUMN-1];
    return !targetData.some(targetRow => targetRow[CHECK_COLUMN-1] === columnA);
  }).map(row => row.concat(`=GOOGLEFINANCE("${row[0]}","price")`));