时间戳

首页 > 解决方案 > 来自另一个电子表格的多个下拉列表，每个下拉列表中有 500 多个项目

list - 来自另一个电子表格的多个下拉列表，每个下拉列表中有 500 多个项目

问题描述

您好，提前感谢您的帮助，

我找到了很多关于此的其他主题，但我没有找到我的问题的答案，所以我在这里。

我的目标是根据另一列中的一个词自动在板中创建下拉列表（50-100 之间的下拉列表）。数据应该在另一个电子表格中，并且大多数下拉列表可以包含大约 600-700 项。请在下面的图片中查看更多说明：

board picture 如果在 F 列“designation”的第一个单词中找到工作表名称，则在 G 和 H 列中创建下拉列表 1 和 2。如果您选择 G 列“Libellés”中的一项，它会在指定表中对“MP”列和价格进行 Vlookup。同样，如果您在“MP”列中选择一个项目，它会在“Libellés”和“价格”中进行 Vlookup 匹配。

当所有工作表都在同一个电子表格中时，这工作正常，但是当所有数据表都在另一个电子表格中时，我找不到让它工作的方法。带有代码的电子表格将被复制很多次（每年 200 多次），所以我想将数据库表（15000+ 线 x 5 列）放在一个电子表格中，当我们打开这些电子表格时，它会提供所有其他“小”电子表格。

我尝试了很多选择：

有没有办法绕过 requireValueInRange 限制？这是我第一次使用谷歌应用脚​​本，所以请不要太苛刻地判断。

这是代码，当所有数据表与下拉列表的目标表位于同一个电子表格中时，它工作正常：

 function onOpen(e) {
     
  // sss for source spreadsheet where I have all datas, and tss for target spreadsheet where I want my lists to be created
  var sss1 = spreadsheetApp.openById('blahblah');
  var ssh1 = sss1.getSheetByName('Emballages');
 
  var tss = SpreadsheetApp.getActiveSpreadsheet();
  var tsh = tss.getActiveSheet();
  var tsh1 = tss.getSheetByName('Fiche_eclate_CS');

     
// The below part is to update 2 lists, using datas from the sheet "emballages" in the source spreadsheet, all working fine except data validation rule, won't accept arguments
   
  var sheet = tss.getSheets()[0]; 
  var column = sheet.getRange("F:F").getValues();
  var prow;
   for (var i = 0; i < column.length; i++){
    if (column[i][0] === "Packaging :") {
      prow = i+1;
      break;     
     }
    }
      
   var emblrow = ssh1.getRange('B:B').getLastRow();
  
       var cel1D = tsh1.getRange(prow,7);
// This is where I have problems. The call ('B2:B'+ emblrow) is not accepted. I can't use this as data validation on another spreadsheet.
       var cel2D = ssh1.getRange('B2:B'+ emblrow); 
       var ruleD = SpreadsheetApp.newDataValidation().requireValueInRange(cel2D).build();
       cel1D.setDataValidation(ruleD);
   
   var cel1M = tsh1.getRange(prow,8);
// Same problem here. The call ('A2:A'+ emblrow) is not accepted.  
   var cel2M = ssh1.getRange('A2:A'+ emblrow ); 
   var ruleM = SpreadsheetApp.newDataValidation().requireValueInRange(cel2M).build();
   cel1M.setDataValidation(ruleM);
     

 // The below part is to update many lists, using datas from one sheet in the source spreadsheet. To find the good sheet in the other spreadsheet, I use the split function. This part is working fine. I only have problems with data validation rules, same as above.
   
   var ui = SpreadsheetApp.getUi();     
   var button = ui.alert("Voulez vous mettre à jour toutes les listes de la feuille ?",ui.ButtonSet.YES_NO);
   
   if (button == ui.Button.YES) {
   
   
  var anchhrow;
  var anchbrow;
  var column1 = tsh1.getRange("B:B").getValues();

   for (var i = 0; i < column1.length; i++){
    if (column1[i][0] === "Anchor-haut") {
      anchhrow = i+3;
      break;     
     }
    } 
    
   for (var i = 0; i < column1.length; i++){
    if (column1[i][0] === "Anchor-bas") {
      anchbrow = i;
      break;     
     }
    }
   
   var rangeH1 = tsh.getRange('H'+anchhrow +':H'+anchbrow).getValues();
   var range = tsh.getRange('H'+anchhrow +':H'+anchbrow);
   var lrowH = rangeH1.length; 

   for (var k=0; k<lrowH; k++){
   
     var rown = anchhrow + k;
     var testrng = sheet.getRange("F" + rown);     
     
     if ( testrng == "" ) {
     }
     else {
       
       var name = nom(tsh,rown);                       
       var ssh3 = sss1.getSheetByName(name);
       
       if (ssh3 == null){
       }
         else {
           
           var flrow = ssh3.getRange('B2:B').getLastRow();
     


            var rng1D = tsh1.getRange(k+anchhrow,7);
// same issu, can't use ('B2:B'+ flrow)for data validation 
            var rng2D = ssh3.getRange('B2:B'+ flrow); 
            var ruleD = 

SpreadsheetApp.newDataValidation().requireValueInRange(rng2D).build();
               rng1D.setDataValidation(ruleD);
       
   
               var rng1M = tsh1.getRange(k+anchhrow,8);
// same here, can't use ('A2:A'+ flrow)for data validation 
               var rng2M = ssh3.getRange('A2:A'+ flrow ); 
               var ruleM = SpreadsheetApp.newDataValidation().requireValueInRange(rng2M).build();
               rng1M.setDataValidation(ruleM);  
                 
           }
      }

  }

     function nom(sheet,row){
       var word = [{}]; 
       var rng = sheet.getRange("F" + row);
       var word = rng.getValue().split(" ");
       Logger.log(word);
       return word[0];       
     }   
   }
  else {
  }   
   
// This part is where I control prices once all lists are updated, this is working fine.   
   
   var ui = SpreadsheetApp.getUi();     
   var button = ui.alert("Les listes ont été mises à jour. Voulez vous mettre à jour les prix ?",ui.ButtonSet.YES_NO);
   
   if (button == ui.Button.YES) {
     
     for(var j=0;j<lrowH; j++){     
   
       var cellI1 = tsh.getRange(j+anchhrow,8).getValue(); 
  
       if (cellI1 == "") {
        }
       else {
         var cellI2 = vlookup(ssh2,1,3,cellI1);   
         tsh.getRange(j+anchhrow,9).setValue(cellI2);

    
    function vlookup(sheet, column, index, value) {

      var lastRow=sheet.getLastRow();
      var data=sheet.getRange(1,column,lastRow,column+index).getValues();
    
      for(i=0;i<data.length;++i){
        if (data[i][0]==value){
          return data[i][index];             
        }
      }
    }
   }
  } 
 }
  else {
  }
  Browser.msgBox ("MAJ feuille terminée");
}

如果您有不明白的内容或需要更多详细信息，请随时提出问题。

提前致谢，

标签： listgoogle-apps-scriptdrop-down-menu

解决方案

我建议

function betweenSpreadsheets() {
  let localSs = SpreadsheetApp.getActiveSpreadsheet();
  
  let distSsUrl = 'https://docs.google.com/spreadsheets/d/1H-Zh blah blah KHP7x8/edit?usp=sharing'; 
  let distSsId = '1H-Zh blah blah KHP7x8';
  let distSs1 = SpreadsheetApp.openById(distSsId)
  let distSs2 = SpreadsheetApp.openByUrl(distSsUrl)
  
}

然后继续进行，就好像工作表在同一个电子表格中一样。

推荐阅读