Running two different scripts in Google Sheets-CodePudding

I'm running two different scripts in my Google Sheet. One of them copies the formatting of the most recent cell into a new cell (I named it Format.gs). The second one sends an email about a new entry (named it Code.gs).

Independently, they run well, but as soon as I add them both into one Google Sheet, the one sending notifications stops working. I'm new to scripts, so any help is much appreciatd.

Here's the code of each script.

Format.gs

/**
 * @OnlyCurrentDoc
 */

const createTrigger = () => {
  ScriptApp.getProjectTriggers().forEach((trigger) => {
    ScriptApp.deleteTrigger(trigger);
  });
  ScriptApp.newTrigger('formRowOnFormSubmit').forSpreadsheet(SpreadsheetApp.getActive()).onFormSubmit().create();
};

const formRowOnFormSubmit = (e) => {
  if (!e) {
    throw new Error('Please do not run this function manually!');
  }
  const { range } = e;
  const row = range.getRowIndex();
  if (row > 2) {
    const sheet = range.getSheet();
    // Select the previous row range
    const sourceRange = sheet.getRange(`${row - 1}:${row - 1}`);
    // Get the recently added row
    const targetRange = sheet.getRange(`${row}:${row}`);
    // Copy the format only from the previous row to the current row
    sourceRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
  }
};

Code.gs

function processEdit(e) {  
  MailApp.sendEmail({
    to: "[email protected]",
    subject: "New entry!",
    body: "Yay! New entry in the Google Sheet."
 
 });
}

CodePudding user response：

The easiest solution might be to remove the createTrigger function and manage the triggers manually.

The above because createTrigger deletes all triggers.

CodePudding user response：

Try it like this:

function upDateFormat(e) {
  if (!e) {
    throw new Error('Please do not run this function manually!');
  }
  if (e.range.rowStart > 2) {
    const sh = e.range.getSheet();
    const sourceRange = sh.getRange(e.range.rowStart - 1,1,1,sh.getLastColumn());
    const targetRange = sh.getRange(e.range.rowStart, 1 ,1, sh.getLastColumn());
    sourceRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
  }
}

function processEdit(e) {  
  const sh = e.range.getSheet();
  const hdr = sh.getRange(1,1,1,sh.getLastColumn()).getValues().flat();
  const msg = hdr.map((h,i) => [`${h}: ${e.values[i]}\n`]);
  Logger.log(msg.join(""));
  MailApp.sendEmail({to: "[email protected]",subject: "New entry!",body: `Yay! New entry in the Google Sheet.\n${msg}`});
} 

function onMyFormSubmit(e) {
  Logger.log(JSON.stringify(e));
  processEdit(e);
  upDateFormat(e);
}

As an alternative I would do the trigger like this:

function createTrigger() {
  if (ScriptApp.getProjectTriggers().filter(t => t.getHandlerFunction() == "onMyFormSubmit").length == 0) {
    ScriptApp.newTrigger("onMyFormSubmit").forSpreadsheet(SpreadsheetApp.getActive()).onFormSubmit().create();
  }
}

And combine both functions into one function.

function onMyFormSubmit(e) {
  if (!e) {
    throw new Error('Please do not run this function manually!');
  }
  //Logger.log(JSON.stringify(e));
  const sh = e.range.getSheet();
  const hdr = sh.getRange(1, 1, 1, sh.getLastColumn()).getValues().flat();
  const msg = hdr.map((h, i) => [`${h}: ${e.values[i]}\n`]).join("");
  MailApp.sendEmail({ to: "[email protected]", subject: "New entry!", body: `Yay! New entry in the Google Sheet.\n${msg}` });
  if (e.range.rowStart > 2) {
    const prev = sh.getRange(e.range.rowStart - 1, 1, 1, sh.getLastColumn());
    const curr = sh.getRange(e.range.rowStart, 1, 1, sh.getLastColumn());
    prev.copyTo(curr, SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
  }
}