1

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

Tanaike
  • 181,128
  • 11
  • 97
  • 165
Humming Bird
  • 39
  • 6
  • It's time for you to start attempting these modifications on your own – Cooper Mar 11 '22 at 18:57
  • Unfortunately, I cannot understand your question and your script. When I saw your showing script, your scripts were not completed and it seems that several functions are shown. I cannot understand what you want to do from these scripts. Can I ask you about the detail of your question? From [your previous question](https://stackoverflow.com/q/71348850), I would like to correctly understand your question and think of the solution. I apologize that I try to correctly understand your question. – Tanaike Mar 12 '22 at 00:44
  • Thanks for asking :) I have cells (E10:E22) that contain calculation formulas and plain numbers. I need to copy, as it is to Datasheet (destination sheet) Right now it is coping only values – Humming Bird Mar 12 '22 at 01:01
  • Thank you for replying and updating your question. When I saw your updated question, there are 2 functions. Unfortunately, I cannot understand the relationship between your 2 functions and your question. I apologize for this. Can I ask you about the detail of your question? – Tanaike Mar 12 '22 at 01:20
  • thanks for replying. one is for submit data (which copies values from Userform to Datasheet) and another function is to search particular data and copy from Datasheet to Userform to related cells. – Humming Bird Mar 12 '22 at 01:31
  • Thank you for replying. From your reply, I couldn't still understand the relationship between your 2 functions and your question. But I would like to try to understand it. When I could correctly understand it, I would like to think of the solution. I would be grateful if you can forgive my poor English skill. – Tanaike Mar 12 '22 at 01:33
  • Thanks for trying. Now I have updated with 1 function. there is no relationship between both functions so I have deleted the other function. hope it helps. Please – Humming Bird Mar 12 '22 at 01:38
  • Thank you for replying. From your replying, I proposed a modification point as an answer. Could you please confirm it? If I misunderstood your question, I apologize. – Tanaike Mar 12 '22 at 01:47

1 Answers1

1

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:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • It works :) thank you so much. Now I need to apply this same action for searchdata function too, which I will raise as a new question. please help that too, because you understand my poor english :) – Humming Bird Mar 12 '22 at 02:02
  • https://stackoverflow.com/questions/71446515/copy-formula-from-source-to-destination-sheet-data-sheet-to-user-form-for-editi – Humming Bird Mar 12 '22 at 02:27
  • Thank you for your Note, I am aware of it. the datasheet(desitnation) data are only for storing and retrieving purposes, the main is Userform which we have to print. Which works great with your modification script. :) thank you – Humming Bird Mar 12 '22 at 02:41