Google-sheets – Google Spreadsheet Alert Scripts

google sheetsgoogle-apps-script

Wanting to add an alert so if the information entered here if not all filled in as required it send you an alert.

function Repair1() {
  var ss        = SpreadsheetApp.getActiveSpreadsheet();
  var r         = ss.getRange("Repair Input!C3:C12");
  var sheet     = ss.getSheetByName("Repair Input"); //Form Sheet
  var datasheet = ss.getSheetByName("Repair Jobs"); //Data Sheet

  var date =          sheet.getRange("C1").getValue();
  var mechName =      sheet.getRange("C3").getValue();
  var customerName =  sheet.getRange("c4").getValue();
  var vehicleName =   sheet.getRange("c5").getValue();
  var group =         sheet.getRange("c6").getValue();
  var jobType =       sheet.getRange("c7").getValue();
  var invoice =       sheet.getRange("c8").getValue();
  var bProfit =       sheet.getRange("c9").getValue();
  var eProfit =       sheet.getRange("c10").getValue();
  var comment =       sheet.getRange("c11").getValue();
  var paid =          sheet.getRange("c12").getValue();
  
  if(mechName != "" && customerName != "" && vehicleName != "" && group != "" && jobType != "" && paid !="") 

//If data is empty here if just wont send but I want it to create an error telling the user there missing data.... But adding .getUi().alert('#ERROR...Please fill out the report completely!'); There causes it to send data that is not all filled in or empty cells breaking the code
   {

  //save dat shit here.
  var newRow = datasheet.appendRow([date, mechName, customerName,vehicleName,group,jobType,invoice,bProfit,eProfit,paid, comment]);

  //Input clear
  var clear = [[ sheet.getRange("C3").clearContent(),
                 sheet.getRange("C4").clearContent(),
                 sheet.getRange("C5").clearContent(),
                 sheet.getRange("C6").clearContent(),
                 sheet.getRange("C7").clearContent(),
                 sheet.getRange("C11").clearContent(),
                 sheet.getRange("C12").clearContent()]];

}
}

Have tried adding something like this but am lost..

SpreadsheetApp.getUi().alert('#ERROR...Please fill out the report completely!');

Best Answer

There are many ways to do this. One of them is to use Array.prototype.some(). I.E. instead of

if(mechName != "" && customerName != "" && vehicleName != "" && group != "" && jobType != "" && paid !="") 
 //If data is empty here if just wont send but I want it to create an error telling the user there missing data.... But adding .getUi().alert('#ERROR...Please fill out the report completely!'); There causes it to send data that is not all filled in or empty cells breaking the code
 {
 ...
 }

use

if( [mechName, customerName, vehicleName, group, obType , paid].some(value ==> value !== '') {
  ...
} else {
  
 SpreadsheetApp.getUi().alert('#ERROR...Please fill out the report completely!');

}

Resources

Array.prototoype.some()

Related Solutions

Google Sheets – How to Reuse Google Apps Scripts in a New Spreadsheet

You can add your script as a library in the new spreadsheet. Lets start from the beginning. This sample code will add a string to the active cell:

function libTest(text) {
  var sh = SpreadsheetApp.getActiveSpreadsheet()
    .getActiveSheet().getActiveCell();
  sh.setValue(text);
}

Save the script, by creating a version:
enter image description here

Next, copy the Project Key from the script editor:
enter image description here

Publish the script as a web app (execute only you):
enter image description here

In the new spreadsheet, open the script editor. Chose Manage Libraries from the resources option and enter and select the key/library:
enter image description here

In the script editor you can now add the following code:

function test(){
  libTest.libTest("jacobjan");
}

Google-sheets – How to subtract two time values in military format in Google Apps Scripts for a spreadsheet

Here's a script that should do what you need:

var MilitaryTime = function(value) {
  this.timeValue = parseInt(value, 10);
  this.hour = Math.floor(this.timeValue / 100);
  this.minute = this.timeValue % 100;
  this.totalMinutes = this.hour * 60 + this.minute;
}
MilitaryTime.fromMinutes = function(totalMinutes){
  var hours = Math.floor(totalMinutes / 60);
  var minutes = totalMinutes % 60;
  return new MilitaryTime(hours * 100 + minutes);
};
MilitaryTime.prototype.subtract = function(otherTime) {
  return MilitaryTime.fromMinutes(this.totalMinutes - otherTime.totalMinutes);
};

function militaryTimeDiff(startTime, endTime) {
  var diff = new MilitaryTime(endTime).subtract(new MilitaryTime(startTime));
  return diff.totalMinutes;
}

The militaryTimeDiff takes as arguments two time values in military format (HHMM) and returns the total number of minutes between them.

You can use it in your spreadsheet formula like this (replace the cell references with the correct ones):

=militaryTimeDiff(A1, A2)

Note: this only works correctly for values within the same day. If you have, for example start time = 2350 and end time = 0125, the function will not realize that the times represent two different days and will return a negative difference.

Best Answer

Related Solutions

Google Sheets – How to Reuse Google Apps Scripts in a New Spreadsheet

Google-sheets – How to subtract two time values in military format in Google Apps Scripts for a spreadsheet

Related Topic