Google Apps Script - Assistance Adding a formula

Question

Formula works on RMA sheet, Cell A2

After Formula on RMA Sheet

I have a Google sheets workbook filled with different sheets (1 main sheet "HelpDesk Tickets"). In the main sheet "HelpDesk Tickets" Column E there is different data based off of ticket categories (8 total) which have sheets named after the categories.

I use a formula which works when ran from the cell A2 in each of the different sheets based off of the ticket categories column "E" from the main sheet (HelpDesk Tickets). I change the sheet name as needed in the formula.

Ex.. From the sheet named "RMA", cell A2, I enter the Formula which copies All rows from the main sheet "HelpDesk Tickets" based off of Column E with "RMA" as a value in any of the cells into the "RMA" sheet. These sheets are used to keep tickets organized for graphs etc. My issue is I cannot get the formula to work when adding it to Apps Script.

Formula (this works)
=query('HelpDesk Tickets'!A:F, "where E = 'RMA' ",0)

I have tried looking at other forums and sites as I feel this should be really simple to add but cannot figure it out for the life of me. Sites Google Support 184049281 Stack Overflow 12036726

I took the example from here and tried to change it with what I need and still did not work

---

↓ Current Script ↓

---

function sheetRMA(){

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheets()[0];

var cell = sheet.getRange("A2"); cell.setFormula("=query('HelpDesk Tickets'!A:F, "where E = 'RMA' ",0)");

}

---

↓ Error ↓

---

Syntax error: SyntaxError: missing ) after argument list line: 7 file: TC_RMA.gs

Answer 1

From ↓ Current Script ↓ of your updated question, unfortunately, I noticed that you don't use my proposed modification. And, I'm worried that my comment might be low readability when the script is put in the comment. So, I would like to add it as an answer. Please modify your script as follows and test it again.

From:

function sheetRMA(){

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheets()[0];

var cell = sheet.getRange("A2"); cell.setFormula("=query('HelpDesk Tickets'!A:F, "where E = 'RMA' ",0)");

}

To:

function sheetRMA() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var cell = sheet.getRange("A2");
  cell.setFormula(`=query('HelpDesk Tickets'!A:F, "where E = 'RMA' ",0)`);
}

Reference:

• Template literals

Added:

From your following reply,

this is what is needed and expected. Sheet2 searches sheet1 column E for any row with the word "RMA". If it finds the word "RMA" it copies the row to sheet2

In this case, how about the following sample script?

Sample script:

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet1 = ss.getSheetByName("Sheet1");
  const sheet2 = ss.getSheetByName("Sheet2");
  const values = sheet1.getRange(2, 1, sheet1.getLastRow() - 1, sheet1.getLastColumn()).getValues().filter(r => r[4].includes("RMA"));
  if (values.length == 0) return;
  sheet2.getRange(sheet2.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);
}