时间戳

首页 > 解决方案 > 查询和时间戳组合脚本不工作 - 调试不显示问题（？）

javascript - 查询和时间戳组合脚本不工作 - 调试不显示问题（？）

问题描述

我正在尝试运行一个脚本，该脚本结合了在编辑单个工作表时添​​加时间戳，并且还放置一个查询以从所有工作表创建一个主信息表 - 两者都在编辑时。

这是我的脚本：

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);
  }
}

我试过移动东西，但无法找到问题所在。

这是一个示例文档

床单经常更换，并添加更多床单。我希望减少公式的数量/加载时间，所以欢迎任何想法/帮助！

标签： javascriptgoogle-apps-scriptgoogle-sheets

解决方案

我已经对您的原始脚本进行了一些更改，以便它不会在没有任何错误的情况下运行，并且两个函数都可以相应地工作。总而言之，这些是我为使您的功能成功工作所做的更改：

  • sheet将变量传递给editTimestamp您使用它而不将其作为函数参数传递（因此它返回无引用错误）。
  • updateMasterSheetRow更改为因为这是您要检查的字符串，并且您已经在函数中定义了这个变量masterSheet。masterSheetName
  • 检查工作表是否与 masterSheet 名称匹配，而不是使用 regex exec，因为它更容易实现并且不太容易出现正则表达式错误。
  • 更改，formula.cell因为这不存在并将其设置为您想要设置公式的正确单元格。

我已经测试过editTimestamp了，效果很好。但是，updateMasterSheetRow工作但没有达到您的目的，因为您需要编辑您formulaText以从您想要的任何工作表中提取正确的信息（目前它只是从工作表末尾的主工作表中提取空信息B224:U225）。

以下是我为解决您的问题而实施的修改的脚本：

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);
  }
}

推荐阅读