I have a script that helps to copy data from the source sheet(UserForm) to the destination sheet(Datasheet), it works great. Now I need an additional task that if E12 to E22 has formula or value, copy as it is. now it gets only value but not formula, I need both. eg. E15 = C15*D15 and E16 = 30, I need to copy both formula and value. Please guide with this below script

function submitdata() {
  const dstSpreadsheetId = "1QL0jaNts2YRkZTlxmS0bk7V1fVVHBsJFmxS5C05PEmA"; // Please set the destination Spreadsheet ID (WB-DataSheet_B).
  const dstSheetName = "DataSheet";
  const srcSheetName = "UserForm";

  // Retrieve values from source sheet and create an array and search value.
  const srcSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const ranges = ["C3", "C7", "C8", "D8", "D6", "D4", "E8", "E19", "E20", "E21", "E22", "B10", "C10", "D10", "E10", "B11", "C11", "D11", "E11", "B12", "C12", "D12", "E12", "B13", "C13", "D13", "E13", "B14", "C14", "D14", "E14", "B15", "C15", "D15", "E15", "B16", "C16", "D16", "E16", "B17", "C17", "D17", "E17", "B18", "C18", "D18", "E18", "B19", "C19", "B20", "C20", "B21", "C21", "B22", "C22","D19", "D20", "D21", "D22"];
  const [search, ...values] = Sheets.Spreadsheets.Values.batchGet(srcSpreadsheet.getId(),{ ranges: ranges.map(r => `'${srcSheetName}'!${r}`) }).valueRanges.flatMap(({ values, range }) => {
  if (range.includes("B10:E18")) return values ? values.flat() : [];
  if (range.includes("B19:D22")) return values ? values.flat() : [];
  return values ? values[0][0] : "";
});
if (!search) return;

  // Put the array to the destination sheet using the search value.
  const dstSheet = SpreadsheetApp.openById(dstSpreadsheetId).getSheetByName(dstSheetName);
  const range = dstSheet.getRange("A2:A"   dstSheet.getLastRow()).createTextFinder(search).findNext();
  if (range) {
    dstSheet.getRange(range.getRow(), 2, 1, values.length).setValues([values]);
  } else {
    dstSheet.getRange(dstSheet.getLastRow()   1, 1, 1, values.length   1).setValues([[search, ...values]]);
  }
}

Also Shared two spreadsheets with actual data (Userform and Datasheet) for your reference https://docs.google.com/spreadsheets/d/1NY_ckzEWxU7DCGro5tTqzpiOi6iG5PAQFxpZg0OKodY/edit?usp=sharing

https://docs.google.com/spreadsheets/d/1QL0jaNts2YRkZTlxmS0bk7V1fVVHBsJFmxS5C05PEmA/edit?usp=sharing

CodePudding user response：

In your situation, how about the following modification?

From:

  const [search, ...values] = Sheets.Spreadsheets.Values.batchGet(srcSpreadsheet.getId(),{ ranges: ranges.map(r => `'${srcSheetName}'!${r}`) }).valueRanges.flatMap(({ values, range }) => {
  if (range.includes("B10:E18")) return values ? values.flat() : [];
  if (range.includes("B19:D22")) return values ? values.flat() : [];
  return values ? values[0][0] : "";
});

To:

const [search, ...values] = Sheets.Spreadsheets.Values.batchGet(srcSpreadsheet.getId(), { ranges: ranges.map(r => `'${srcSheetName}'!${r}`), valueRenderOption: "FORMULA" }).valueRanges.flatMap(({ values, range }) => {
  if (range.includes("B10:E18")) return values ? values.flat() : [];
  if (range.includes("B19:D22")) return values ? values.flat() : [];
  return values ? values[0][0] : "";
});

• In this modification, valueRenderOption: "FORMULA" is added. By this, when the cell has the formula, the formula can be retrieved instead of the display value.

Note:

• By the way, as the additional information, in your situation, for example, when "E22" of the source sheet has the formula of =SUM(E19:E21), the retrieved formula is =SUM(E19:E21). So when this is put to the cell of the destination sheet, this formula is used. In this case, I think that the result value of the formula is different from that of the source sheet. Because the stducture of sheet is different between the source sheet and destination sheet. If you want to change this formula for the destination sheet, I think that the script will be complicated. Please be careful about this.
  • From your question of Now I need an additional task that if E12 to E22 has formula or value, copy as it is. now it gets only value but not formula, I need both. eg. E15 = C15*D15 and E16 = 30, I need to copy both formula and value., I understood that you just wanted to the formulas from the source sheet to the destination sheet.

Reference:

• Method: spreadsheets.values.batchGet