我正在尝试在Google表格中创建一个脚本，该脚本将检查一个选项卡上的列中的值与另一选项卡中的列中的值。
例如;我想在一个选项卡（“用户”）中有一个允许电子邮件地址的列表，然后从表单中的另一个选项卡（“ Form1”）中输入地址。
如果表单中的地址不在“允许”列表中，我希望它自动删除该行。
我认为我的主要问题与此代码。
if( entryValues[i] === emailsAllowed[t] )
即使数组中的值匹配，它也永远不会变为真。
function onOpen()
{
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var s = sheet.getSheetByName('Form1'); //Get the sheet with the entered records in it.
var s2 = sheet.getSheetByName('Users');//Get the sheet with the Allow list in it.
var entryValues = s.getRange('C2:C9').getValues(); //(Get the list of e-mail addresses entered via the form (using their cell reference))
var emailsAllowed = s2.getRange('A2:A6').getValues(); //(Get the list of allowed e-mail addresses (using their cell reference))
var isAllowed = 0;
for(var i = entryValues.length-1; i > 1 ; i--) // work from the bottom up so when you delete a row it will not miss other rows or delete a row it should not.
{
var valueE = entryValues[i] // Test Variable to see what the value is (Can delete this line)
for(var t = 0; t < emailsAllowed.length; t++) //Loop through all the records in the allow list
{
var valueA = emailsAllowed[t] // Test Variable to see what the value is (Can delete this line)
if( entryValues[i] === emailsAllowed[t] ) // Check to see if there is a match between what was entered and what is in the allow list. //!!! For some reason this line is never true !!!//
{ isAllowed = 1;} //if it is there set a variable isAllowed to 1 (to indicate that there is a match)
}
if( isAllowed === 1 )
{isAllowed = 0; }// if there was a match set isAllowed back to 0, ready for the next run
else
{
s.deleteRows(i) //if there was no match (and isAllowed is not set) delete the invalid row.
}
}
}
谢谢
更新：这是工作代码：感谢（Sandy Good）
function onOpen()
{
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var s = sheet.getSheetByName('Form1'); //Get the sheet with the entered records in it.
var s2 = sheet.getSheetByName('Users');//Get the sheet with the Allow list in it.
var entryValues = s.getRange('C2:C9').getValues().join().split(","); //(Get the list of e-mail addresses entered via the form (using their cell reference))
var emailsAllowed = s2.getRange('A2:A6').getValues().join().split(","); //(Get the list of allowed e-mail addresses (using their cell reference))
var isAllowed = 0; // Create the variable isAllowed and set it to 0 (this will be used to leave the row if it matches)
for(var i = entryValues.length-1; i > 1 ; i--) // work from the bottom up so when you delete a row it will not miss other rows or delete a row it should not.
{
var valueE = entryValues[i] // Test Variable to see what the value is (Can delete this line)
for(var t = 0; t < emailsAllowed.length; t++) //Loop through all the records in the allow list
{
var valueA = emailsAllowed[t] // Test Variable to see what the value is (Can delete this line)
if( entryValues[i] === emailsAllowed[t] ) // Check to see if there is a match between what was entered and what is in the allow list. //!!! For some reason this line is never true !!!//
{ isAllowed = 1;} //if it is there set a variable isAllowed to 1 (to indicate that there is a match)
}
if( isAllowed === 1 )
{isAllowed = 0; }// if there was a match set isAllowed back to 0, ready for the next run
else
{
s.deleteRow(i+2) //if there was no match (and isAllowed is not set) delete the invalid row. (+ 2 because the rows start from 1 and they also have a heading (the the array starts from 0))
}
}
}
最佳答案
getValues()方法返回一个二维数组。您没有获得内部数组的值。代码不是将值与值进行比较，而是将数组与数组进行比较。内部数组中只有一个值，但它仍然是一个数组。
您需要从二维数组中获取值。当前代码如下：
var entryValues = s.getRange('C2:C9').getValues();
var emailsAllowed = s2.getRange('A2:A6').getValues();
要将2D数组转换为常规数组，请执行以下操作：
var entryValues = s.getRange('C2:C9').getValues().join().split(",");
var emailsAllowed = s2.getRange('A2:A6').getValues().join().split(",");
可以使用此方法，因为每个内部数组中只有一个值，这是因为您仅获得一列。如果要获取多列，则该技术将不起作用。
twoDArray = [ [innerValue], [innerValueTwo], [inner3] ]
关于javascript - Google表格删除行，如果一个选项卡上的列中的值与其他选项卡上的列不匹配，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/33447274/