Query & Timestamp Combined Script Not Working – Debug doesn’t show issue(?)

I am attempting to run a script that combines adding a timestamp upon edit to individual sheets, and also to place a query to create a master sheet of info from all sheets – both upon edit.

Here’s the script I have:

function onEdit(e) {

  const masterSheet = "1-Cover Sheet";
  const range = e.range;
  const sheet = range.getSheet();
  const name = sheet.getName();
  const currentDate = new Date();
  
  editTimestamp(e, range);
  updateMasterSheetRows(e, masterSheet);
}

function editTimestamp(e, range) {

  var startRow = 1;
  var row = e.range.getRow();
  var col = e.range.getColumn();
  var currentDate = new Date();
  
  if(col === 7 && row === 3 && sheet.getRange(1,14).getValue() == "" && 
     sheet.getName() != "4-Transfer Walkthru" 
         sheet.getRange(1,14).setValue(currentDate);
       }
  
  else if(row >= startRow && 
     sheet.getName() != "4-Transfer Walkthru" && 
     sheet.getName() != "1-Cover Sheet"
    
    sheet.getRange(2,14).setValue(currentDate);
         }  
  }


function updateMasterSheetRows(e, masterSheet) { 
  const masterSheetName = "1-Cover Sheet";
  const dataLocation = "B224:U225";

  
  const formulas = [
    {
      location: "B4",
      code: "is not null",
    },
    ];
  

  const sheets = e.source.getSheets();
  
  
  let dataRangeParts = [];
  for (const sheet of sheets) {
    
    const name = sheet.getSheetName();

    if (!masterSheet.exec(name)) continue;

    dataRangeParts.push(`'${name}'!${dataLocation}`);
  }
  const dataRange = dataRangeParts.join(";");
    
  for (const formula of formulas) {

    const query = `SELECT * WHERE Col1'${formula.code}'`;
    const formulaText = `IFERROR(QUERY({${dataRange}},"${query}"),{"","",""})`;
    
    formula.cell = masterSheet.getRange(formula.location);
    formula.cell.setFormula(formulaText);
  }
}

I’ve tried moving things around, but haven’t been able to locate where the issue is.

Here’s a sample doc

The sheets frequently change, with more being added. I’m looking to lessen the amount of formulas / loading time with this, so any ideas/help welcome!

Answer

I have made some changes to your original script so that it doesn’t run without any errors and so that both functions work accordingly. To sum up these are the changed I did to get your funcion to work successfully:

• Passed the sheet variable to editTimestamp as you were using it without passing it as a function argument (and thus it was returning a no reference error).
• In updateMasterSheetRow changed masterSheet to masterSheetName as this is the string you want to be checking and you already had defined this varaible in the function.
• Checked if the sheet matched with the masterSheet name instead of using regex exec as it was of easier implementation and less prone to regular expression errors.
• Changed the formula.cell as this does not exist and set it to the right cell you want to set the formula to instead.

I have tested editTimestamp and it worked perfectly. However, updateMasterSheetRow worked but did not achieve your purpose as you will need to edit your formulaText to pull the right information from whichever sheet you want (currently it simply pulls empty information from the master sheet at the end of the sheet B224:U225).

Below is your script with the modifications I implemented to solve your issues:

function onEdit(e) {
  const masterSheet = "1-Cover Sheet";
  const range = e.range;
  const sheet = range.getSheet();
  const name = sheet.getName();
  const currentDate = new Date();
  
  // pass sheet to editTimestamp as a parameter
  editTimestamp(e, range, sheet);
  updateMasterSheetRows(e);
}

// receive the modified sheet
function editTimestamp(e, range,sheet) {

  var startRow = 1;
  
  var row = e.range.getRow();
  var col = e.range.getColumn();
  var currentDate = new Date();
  if(col === 7 && row === 3 && sheet.getRange(1,14).getValue() == "" && 
     sheet.getName() != "4-Transfer Walkthru" && 
     sheet.getName() != "1-Cover Sheet" && 
     sheet.getName() != "2-Inventory" && 
     sheet.getName() != "3-Template"){
          Logger.log("1");
         sheet.getRange(1,14).setValue(currentDate);
       }
  
  else if(row >= startRow && 
     sheet.getName() != "4-Transfer Walkthru" && 
     sheet.getName() != "1-Cover Sheet" && 
     sheet.getName() != "2-Inventory" && 
     sheet.getName() != "3-Template"){
    
    sheet.getRange(2,14).setValue(currentDate);
       
  }  

  
  }


function updateMasterSheetRows(e) { 
  const masterSheetName = "1-Cover Sheet";
  const dataLocation = "B224:U225";

  const formulas = [
    {
      location: "B4",
      code: "is not null",
    },
    ];
  

  const sheets = e.source.getSheets();
  

  let dataRangeParts = [];
  for (const sheet of sheets) {
    const name = sheet.getSheetName();

  // check that the sheet is not the same as the master sheet
    if(masterSheetName!=name){continue}

    dataRangeParts.push(`'${name}'!${dataLocation}`);
  }
  const dataRange = dataRangeParts.join(";");
    
  for (const formula of formulas) {

    const query = `SELECT * WHERE Col1 ${formula.code}`;
    const formulaText = `IFERROR(QUERY({${dataRange}},"${query}"),{"","",""})`;
    
    // change it so that the right cell is modified onEdit
    var cell = SpreadsheetApp.getActive().getSheetByName(masterSheetName).getRange(formula.location);
    cell.setFormula(formulaText);
  }
}