0

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);
  }
}
Iamblichus
  • 18,540
  • 2
  • 11
  • 27
NSW
  • 13
  • 4
  • Hi, I'm not sure what's your desired output. Do you mean the `Source` sheet has a series of stock codes in column A, and you want to write the formulas `=GOOGLEFINANCE(A1,price)` to a column in the target sheet? Also, where does `price` come from? – Iamblichus Apr 28 '22 at 08:07
  • Consider providing a copy of the spreadsheet so that the desired output is clarified. – Iamblichus Apr 28 '22 at 08:35
  • Yes, Source sheet has the stocks codes. where does price come from? Its a googlefinance syntax. – NSW Apr 28 '22 at 10:43
  • https://docs.google.com/spreadsheets/d/13NGHmUjnISK76wri8x9PoyU34KPLdW2TrOXVvoY5kuM/edit#gid=411614999 Sample spreadsheet – NSW Apr 28 '22 at 10:45

1 Answers1

0

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")`));
Iamblichus
  • 18,540
  • 2
  • 11
  • 27