Home > Back-end >  Copy Formula from source to destination sheet (Data sheet to User form for editing purpose)
Copy Formula from source to destination sheet (Data sheet to User form for editing purpose)

Time:03-12

I have a script that helps to search and copy data from Datasheet to Userform sheet to related cells. now it gets only value but not formula, I need to copy both formula and value to destinated cells.

function searchdata() {
  const srcSpreadsheetId = "1QL0jaNts2YRkZTlxmS0bk7V1fVVHBsJFmxS5C05PEmA"; // Please set the source Spreadsheet ID (WB-DataSheet).
  const srcSheetName = "DataSheet";
  const dstSheetName = "UserForm";

  // Retrieve values from source sheet and create an array and search value.
  const dstSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const dstSheet = dstSpreadsheet.getSheetByName(dstSheetName);
  const search = dstSheet.getRange("B1").getValue();

  // Search the value.
  const srcSpreadsheet = SpreadsheetApp.openById(srcSpreadsheetId);
  const srcSheet = srcSpreadsheet.getSheetByName(srcSheetName);
  const range = srcSheet.getRange("A2:A"   srcSheet.getLastRow()).createTextFinder(search).findNext();
  if (!range) {
    SpreadsheetApp.getUi().alert('UserForm Number Not Found');
  }

  // Retrieve the values from the searched row.
  const values = srcSheet.getRange(range.getRow(), 1, 1, srcSheet.getLastColumn()).getValues()[0];

  // Put the values to the cells of "UserForm" sheet.
  const expandRangeList = ["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 data = expandRangeList.map((range, i) => ({ range, values: [[values[i] || ""]] }));
  Sheets.Spreadsheets.Values.batchUpdate({ data, valueInputOption: "USER_ENTERED" }, dstSpreadsheet.getId());
}

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 this case, how about the following modification?

From:

const values = srcSheet.getRange(range.getRow(), 1, 1, srcSheet.getLastColumn()).getValues()[0];

To:

const r = srcSheet.getRange(range.getRow(), 1, 1, srcSheet.getLastColumn()).getA1Notation();
const values = Sheets.Spreadsheets.Values.get(srcSpreadsheetId, `'${srcSheetName}'!${r}`, {valueRenderOption: "FORMULA"}).values[0];
  • In this case, the values are retrieved from srcSheet.getRange(range.getRow(), 1, 1, srcSheet.getLastColumn()) using Sheets API with valueRenderOption: "FORMULA". By this, the values and formulas can be retrieved.

Reference:

  • Related