I'm working on an app that scans data into Google sheets. i have two sheets: sheet1 and sheet2. In sheet1 the data is entered into it after a scan is performed. But before the scan data can be entered, i would like to do an if function to check if there is a similar type of data in sheet2 before entering the data. if there isn't one then a message will be displayed such as "There is no such person in the database". I would also like to specify the column to check for similarity in sheet2 e.g A2:A100 Below is the script code

function doGet(e){
var ss = SpreadsheetApp.openByUrl("google sheet url");
//Give your Sheet name here
var sheet = ss.getSheetByName("Sheet1");

return insert(e,sheet);

}

function doPost(e){
var ss = SpreadsheetApp.openByUrl("google sheet url");
//Give your Sheet name here
var sheet = ss.getSheetByName("Sheet1");

return insert(e,sheet);

}



function insert(e,sheet){

 // reciving scanned data from client i.e android app
 var scannedData = e.parameter.sdata;
 var d = new Date();
 var ctime= d.toLocaleString();

 sheet.appendRow([scannedData,ctime]);

 return ContentService
.createTextOutput("Success")
.setMimeType(ContentService.MimeType.JAVASCRIPT);  


}

Thanks.

I'm working on an app that scans data into Google sheets. i have two sheets: sheet1 and sheet2. In sheet1 the data is entered into it after a scan is performed. But before the scan data can be entered, i would like to do an if function to check if there is a similar type of data in sheet2 before entering the data. if there isn't one then a message will be displayed such as "There is no such person in the database". I would also like to specify the column to check for similarity in sheet2 e.g A2:A100 Below is the script code

function doGet(e){
var ss = SpreadsheetApp.openByUrl("google sheet url");
//Give your Sheet name here
var sheet = ss.getSheetByName("Sheet1");

return insert(e,sheet);

}

function doPost(e){
var ss = SpreadsheetApp.openByUrl("google sheet url");
//Give your Sheet name here
var sheet = ss.getSheetByName("Sheet1");

return insert(e,sheet);

}



function insert(e,sheet){

 // reciving scanned data from client i.e android app
 var scannedData = e.parameter.sdata;
 var d = new Date();
 var ctime= d.toLocaleString();

 sheet.appendRow([scannedData,ctime]);

 return ContentService
.createTextOutput("Success")
.setMimeType(ContentService.MimeType.JAVASCRIPT);  


}

Thanks.

• javascript
• if-statement
• google-apps-script
• google-sheets

Share Improve this question Follow edited Nov 20, 2017 at 21:51 Wicket 38.7k99 gold badges8080 silver badges195195 bronze badges asked Nov 20, 2017 at 9:30 TazzaTazza 3511 gold badge22 silver badges99 bronze badges 1

• Possible duplicate of how to use if / else – Wicket Commented Nov 20, 2017 at 21:48

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 1

Google Apps Script is in essence JavaScript with different libraries. So your if statements are in essence all the same as there:

if (a1 == a2) {
  a1 = 0
}
else if (a1 != a3) {
  a1 = 1
}
else {
  a1 = 'String'
}

Check on logic operators to find what you can do if you are unfamiliar with that and also read about the switch satement as it's also sometimes useful in place of if statements (not in your case, but might as well read up on it while you are on the topic).

EDIT:

As per your ment, to pare the data from specific columns it's quite simple and there are multiple ways of doing it. Think of it as a structure Spreadsheet → Sheet → Range → Value. So if you already have an object for the sheet you can do range = sheet1.getRange(1,1) which will get you the cell A1. Or you can use A1 notation for the getRange('A1'). If your range is a single cell you can then do range.getValue() which will return the value inside the cell.

Now you want to find if the data exists in another sheet, going 1 by 1 will not be effective as getValue() will bloat the script very quickly. Instead you might want to do vals = sheet2.getDataRange().getValues(). This will return a 2D array of all the values inside of the sheet. If you want to only check a specific column and you know you do not care about the rest you can just replace getDataRange() with something like .getRange(C:C) or the same would be getRange(1, 3, sheet2.getLastRow(), 1).

Then you will simply loop through the 2D array with vals[rowNum][colNum].

If the value is added manually to 1 cell and the script fires with an onEdit trigger, you can also get the value directly from the event object e where it's in onEdit(e).

Read up on getRange() (read those bellow as well) as well as getValue() (and getValues bellow that). Google has excellent documentation, just logically follow the structure for what you want to achieve.