CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
TTDA
TTDA

Reputation: 69

if equal to value on another spreadsheet

i have a system in place where users enter information on a google form and an appscript puts the info on a calendar.

i'm wonder if there's a possibility to make the appscript check if the information entered matches anything from another spreadsheet (ie: a banned information blacklist)

I figure i can do something like:

var name = sheet.getRange(lr,name,1,1).getValue();
var ban = SpreadsheetApp.openById('ID of another sheet').getValue();
if (name == ban){
return;
};

but apparently i'm wrong, and it doesn't work.
i think there's two problems with what i'm trying to do:
1.the code to get values from another sheet doesn't work
2.the values from another sheet isn't a single value, but multiple names in a column, so i can't compare the variables name and ban (either that or the values can't be written into a single variable)

I remember i saw some code that can save multiple values in one variable, i think it looks like

var ban=i,i++;

or something among those lines, but what i typed doesn't seem to be correct, as it doesn't work.

I'm wonder how to get the values from another spreadsheet and write multiple values into a single variable.

I apologize if i made some stupid error in the code, i don't actually know how to code, i'm just googling and mixing codes i find online.
Thanks

Upvotes: 0

Views: 1337

Answers (1)

Suyash Gandhi
Suyash Gandhi

Reputation: 936

First of all, I am guessing you've defined variable sheet something like this:

var sheet = SpreadsheetApp.openById("ID of sheet where the data is stored").getSheetByName("Name");

Next step would be to define variable ban like this:

var ban = SpreadsheetApp.openById("ID of another sheet").getSheetByName("Name").getDataRange().getValues();

Now the part where the information is matching or not is checked.

var count=0;

for(var i=0;i<ban.length;i++)
{
   if(name==ban[i])
   {
      count++;
   }
}

if(count!=0)
{
   Logger.log("Match found");
}

To check the output of the code when you run it you need to press CTRL+ENTER...this will open the Logs where if you find the Log as "Match Found"...it means the code is correct...else there might be some other error...maybe the data format of the spreadsheet where the Banned values are stored.

Upvotes: 3

Related Questions