javascript 如何搜索和匹配，如果一个工作表名称从一个单元格值在另一个工作表在谷歌应用程序脚本和复制单元格

我有三张床单，如果用户电子邮件地址与QUOTATIONS工作簿中的工作表名称匹配，则从该工作表中获取P列中指定产品的价格。如果未找到工作表名称，则从PRODUCTS工作表中获取该产品的价格。价格还取决于在工作表中指定的货币。预期结果的示例可在JOBS工作表的Q列https://docs.google.com/spreadsheets/d/1up7cUvQqL-LcA7EeuM65B0zjcRT46LGwN2x3C_4j0bY/edit?usp=sharing中查看。如果用户有报价，并且电子邮件与工作表名称匹配，则价格来自QUOTATIONS工作簿https://docs.google.com/spreadsheets/d/10tb0zE_8i849T-hL6mU-Pw_4V-aMzNZJGNOFYG1F3qk/edit?usp=sharing。如果用户没有报价，并且没有匹配的电子邮件地址，则价格来自来自产品表https://docs.google.com/spreadsheets/d/1pt7YnN9fmoD4PE0o9oVPezK8Qz6ZmabyJbWXfdzFeMU/edit?usp=sharing
我有以下脚本，它确实匹配产品表和工作表之间的价格，但无法让它也检查是否存在表名称，它匹配工作表中的电子邮件地址，然后从该表中获得价格。感谢帮助，谢谢

function updateQuotationPrice() {
  
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var jobSheet = ss.getSheetByName('JOBS');
  var productSheet = SpreadsheetApp.openById("1pt7YnN9fmoD4PE0o9oVPezK8Qz6ZmabyJbWXfdzFeMU").getSheetByName('PRODUCTS');
  var quotationSheet = SpreadsheetApp.openById("10tb0zE_8i849T-hL6mU-Pw_4V-aMzNZJGNOFYG1F3qk");
  var sheetNames = []
 var sheets = quotationSheet.getSheets()//.forEach(function(sheet){sheets[sheet.getName()]=sheet;});
  sheets.forEach(function (sheet) {
    sheetNames.push(sheet.getName());
  console.log(sheets)
  });

  var objEuro = productSheet.getRange("A2:D" + productSheet.getLastRow()).getValues().reduce((o, r) =>  (o[r[0]] = r[3], o), {});
  var objSterling = productSheet.getRange("A2:D" + productSheet.getLastRow()).getValues().reduce((o, r) =>  (o[r[0]] = r[2], o), {});
  var objQuotationEuro = sheets.getRange("A2:D" + sheets.getLastRow()).getValues().reduce((o, r) =>  (o[r[0]] = r[3], o), {}); 
  var objQuotationSterling = sheets.getRange("A2:D" + sheets.getLastRow()).getValues().reduce((o, r) =>  (o[r[0]] = r[2], o), {});
  var range = jobSheet.getRange("O2:AK" + jobSheet.getLastRow());
  var euroValues = range.getValues().map(r => [objEuro[r[0]] || null]);
  var sterlingValues = range.getValues().map(r => [objSterling[r[0]] || null]);
  var quotationEuroValues = range.getValues().map(r => [objQuotationEuro[r[0]] || null]);
  var quotationSterlingValues = range.getValues().map(r => [objQuotationSterling[r[0]] || null]);
  var vs = jobSheet.getRange("O2:R"+ jobSheet.getLastRow()).getValues();

  vs.forEach((r) => {  
    
    if(r[0] == "EURO" && r[1].match(/^A/i) && sheets != r[3]  ){
  range.offset(0, 2, euroValues.length, 1).setValues(euroValues)};
  
  if(r[0] == "STERLING" && r[1].match(/^A/i)  && sheets != r[3]){
  range.offset(0, 2, sterlingValues.length, 1).setValues(sterlingValues)}
  
   if(r[0] == "EURO" && r[1].match(/^A/i) && sheets == r[3] ){
  range.offset(0, 2, quotationValues.length, 1).setValues(quotationEuroValues)}

  if(r[0] == "STERLING" && r[1].match(/^A/i) && sheets == r[3] ){
  range.offset(0, 2, quotationValues.length, 1).setValues(quotationSterlingValues)}
  });
}

建议

我创建了另一种方法，其中我仍然使用forEach()函数来处理数据，但是，我使用filter()和includes()函数来帮助数据匹配过程。

脚本

您可以使用以下脚本作为脚本的基础。

function updateQuotationPrice() {
  var ss1 = SpreadsheetApp.openById("User Update Sheet ID"); //User Update
  var ss2 = SpreadsheetApp.openById("Quotation Sheet ID"); //Quotation
  var ss3 = SpreadsheetApp.openById("Projects Sheet ID"); //Projects

  //Extract data from User Update
  var lr1 = ss1.getSheetByName("Jobs").getLastRow();
  var data1 = ss1.getSheetByName("Jobs").getRange(2, 13, lr1 - 1, 4).getValues();

  //Extract data from Quotation Spreadsheet
  var sheetNames2 = [];
  ss2.getSheets().forEach(x => sheetNames2.push(x.getName()));

  //Process data 1
  var output = [];
  data1.forEach(x => {
    if (sheetNames2.includes(x[0]) && ss2.getSheetByName(x[0]).getDataRange().getValues().filter(y => y.includes(x[3])).length > 0) { // added new condition here
      if (x[2] == "EURO") {
        output.push([ss2.getSheetByName(x[0]).getDataRange().getValues().filter(y => y.includes(x[3]))[0][3]]);
      }
      else if (x[2] == "STERLING") {
        output.push([ss2.getSheetByName(x[0]).getDataRange().getValues().filter(y => y.includes(x[3]))[0][2]]);
      }
    }
    else {
      if (x[2] == "EURO") {
        output.push([ss3.getSheetByName("Products").getDataRange().getValues().filter(y => y.includes(x[3]))[0][3]]);
      }
      else if (x[2] == "STERLING") {
        output.push([ss3.getSheetByName("Products").getDataRange().getValues().filter(y => y.includes(x[3]))[0][2]]);
      }
    }
  });

  //Output
  ss1.getSheetByName("Jobs").getRange(2, 17, lr1 - 1, 1).setValues(output);
}

javascript 如何搜索和匹配，如果一个工作表名称从一个单元格值在另一个工作表在谷歌应用程序脚本和复制单元格

1条答案

建议

脚本

输出

参考文献

相关问题

热门标签

最新问答