sobota 4. dubna 2015

Pre-filled google form

How to create a pre-filled form URL from Google Form?


















ID-Form = your form ID
1127584552 = Id  form Html

https://docs.google.com/forms/d/ID-form/viewform?entry.1127584552=Pavel


sobota 28. března 2015

Create user DialogBox

How to create user dialogBox in Spreadsheet.

Example 1

old version of Spreadsheet.
function showURLDialogBox(){
  var app = UiApp.createApplication().setHeight(40).setWidth(200);
  app.setTitle("The best URL");
  var link1 = app.createAnchor('office.lasakovi.com', 'http://office.lasakovi.com/');
   // add widgets to panels
  app.add(app.createVerticalPanel().add(link1)); 
  var doc = SpreadsheetApp.getActive();
  doc.show(app);
}

 








Example 2

function showURLDialogBox2(){
  var app = UiApp.createApplication().setHeight(60).setWidth(200);
  app.setTitle("The best URL");
  var link1 = app.createAnchor('office.lasakovi.com', 'http://office.lasakovi.com/');
  var link2 = app.createAnchor('HowToExcel', 'http://JakNaExcel.cz/');

  app.add(app.createVerticalPanel().add(link1).add(link2));
  var doc = SpreadsheetApp.getActive();
  doc.show(app);






 

Example 3

new version

function showPrompt() {
  // New version
  var ui = SpreadsheetApp.getUi();

  var result = ui.prompt(
      'Please enter your name:',
      ui.ButtonSet.OK_CANCEL);

  var button = result.getSelectedButton();
  var text = result.getResponseText();

  if (button == ui.Button.OK) {
    // if clicked "OK".
    ui.alert('Your name is ' + text + '.');
  } else if (button == ui.Button.CANCEL) {
    // if is clicked "Cancel".
    ui.alert('I didn\'t get your name.');
  } else if (button == ui.Button.CLOSE) {
    // if is X in the title bar.
    ui.alert('You closed the dialog.');
  }
}

 

pátek 27. března 2015

Send Email - script apps

Send email - apps script google
How to send email using apps script google.
Note: nospam@example.cz change to your real email adress!

First email for test

You can use MailApp or GmailApp

function Email1() {
   MailApp.sendEmail('nospam@example.cz', "New Email", "Hello World");
}







function Email2() {
   GmailApp.sendEmail('nospam@example.cz', "New Email 2", "Hello World");
}










Email - send html code


function EmailHtml1() {
  var emailAddress = '
nospam@example.cz';
  var folderURL = 'http://office.lasakovi.com';

  var message = "<HTML><BODY>"
      + "<P>" + " My message."
      + '<P>The Web about Microsoft Office <A HREF="' + folderURL + '">here</A>. In Czech language.'
      + "</HTML></BODY>";

  MailApp.sendEmail(emailAddress, "Test email Html", "", {htmlBody: message});
}








čtvrtek 26. března 2015

Custom menu (create, delete,...)

How to create custom menu in google Apps.

Create custom menu

Example 1. Menu is create in Active Spreadsheet.

function CreateMenu1() {
 var ss = SpreadsheetApp.getActiveSpreadsheet(),
     options = [
      {name:"Run First", functionName:"scrFirst"},
      {name:"Run Two", functionName:"scrSecond"}
     ];
 ss.addMenu("My menu", options);
}

function scrFirst() {
     Browser.msgBox("First menu script.");
}

function scrSecond() {
     Browser.msgBox("Second mune script.");
}



Create custom menu 2

Example 2. Menu is create in Active Spreadsheet.

function CreateMenu2() {
  var ss = SpreadsheetApp.getUi();

  ss.createMenu('Custom Menu')
      .addItem('First item', 'menuItem1')
      .addSeparator()
      .addSubMenu(ss.createMenu('Sub-menu')
          .addItem('Second item', 'menuItem2'))
      .addToUi();
}

function CreateMenu3() {
var ss = SpreadsheetApp.getUi();

  ss.createMenu('Custom Menu')
      .addItem('First item', 'menuItem1')
      .addSeparator()
      .addItem('Second item', 'menuItem2')
      .addToUi();
}



Remove custom menu 2

Remove "Custom Menu" form Example 2

function RemoveMenu1() {
     var ss = SpreadsheetApp.getActiveSpreadsheet();
     ss.removeMenu('Custom Menu');
}

If Then Else

How to use If, else If, else

if

function IfThen1() {
   var i = 1;
 
   if (i == 1) {
      Browser.msgBox( 1 );
    }
}

if, else

function IfThen2() {
   var i = 2;
 
  if (i == 1) {
      Browser.msgBox( 1 );
    }
    else {
      Browser.msgBox( "No 1." );
    }
}


if, if else, else 

function IfThen3() {
   var i = 2;
 
  if (i == 1) {
      Browser.msgBox( 1 );
    }
      else if (i == 2) {
      Browser.msgBox("Number Two.");
    }
    else {
      Browser.msgBox( "No 1, 2." );
    }
}

úterý 24. března 2015

For next loop step up down

How to use for in google asp script
  • Countup step = 1 
  • Countup step = 2 or more
  • Countdown step = 1

For with Step 1

function forTest1() {
     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var sheet = ss.getSheets()[0];

  for (var i = 1; i < 5; i++) {
    var range = sheet.getRange(8,i);
    var data = range.setValue(i); 
  }
}


For with Step 2

function forTest2() {
     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var sheet = ss.getSheets()[0];

  // step = 2 (or more)
  for (var i = 1; i < 10; i=i+2) {
    var range = sheet.getRange(9,i);
    var data = range.setValue(i); 
  }

}

For with countdown loop

function forTest3() {
     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var sheet = ss.getSheets()[0];

  for (var i = 10; i > 3; i=i-1) {
    var range = sheet.getRange(11,i);
    var data = range.setValue(i); 
  }
}

How to set design cell cells

How to design cell, cells (Font Family, Background, Color, Size,...)

Method

  • setBackgroundRGB
  • setBackground
  • setBorder
  • setDataValidation
  • setDataValidations
  • setFontColor
  • setFontColors
  • setFontFamilies
  • setFontFamily
  • setFontLine
  • setFontLines
  • setFontSize

setBackgroundRGB(red, green, blue)

function BackgroundColor2() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0];
    var cell = sheet.getRange("B3");
    cell.setBackgroundRGB(200, 115, 120);
}

setBackground(color)

function BackgroundColor1() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0];
    var cell = sheet.getRange("B2");
    cell.setBackground("blue");
}


setBorder(top, left, bottom, right, vertical, horizontal)


  • null
  • true
  • false
function Border1() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0];
    var cell = sheet.getRange("C3");
    cell.setBorder(true, null, true, null, false, false);
}


function Border2() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0];
  var cell = sheet.getRange("D7:E8");
    cell.setBorder(null, null, null, null, true, true);
}



setDataValidation(rule)

in work


setDataValidations(rules)

in work

setFontColor(color)

For one cell, more cells (range) - setFontColors

function FontColorMy2() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 var cell = sheet.getRange("A16");
 cell.setFontColor("yellow");
}



setFontColors(colors)

For more cells (range), for one cell - setFontColor

function FontColorMy2() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0];

   // set for Colors
   var colors = [
         ["red", "black", "blue"],
         ["#FF0000", "#000000", "#0000FF"]  ];

   var cell = sheet.getRange("B7:D8");
   cell.setFontColors(colors);
}



setFontFamilies(fontFamilies)

in work

setFontFamily(fontFamily)

function myFontFamily() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 var cell = sheet.getRange("B2");
 cell.setFontFamily("Helvetica");
}


  • Arial
  • Helvetica
  • Times New Roman
  • Georgia
  • Verdana
  • ....

setFontLine(fontLine)

For one cell (range), for more cells - setFontLines 

  • underline
  • line-through
  • none
function myFontLine1() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0];
    var cell = sheet.getRange("B2");
    cell.setFontLine("line-through");      // Note in Czech: přeškrtnuté
}



setFontLines(fontLines)

For more cells (range), for one cell - setFontLine
  • underline
  • line-through
  • none
function myFontLine2() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0];
    var cell = sheet.getRange("A2:B3");
 
  var linesMy = [
      ["underline", "none"],
      ["underline", "line-through"] //
   ];
  

   cell.setFontLines(linesMy);
}

 

setFontSize(size)

function FontSizeMy1() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 var cell = sheet.getRange("B2");
 cell.setFontSize(20); 
}

Write value in cell / cells

How to write value in cells with Google Apps script


write to cells of the active workbook

function TextToCell1() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange(1,3);
  var data = range.setValue('css004');
}



write a new line in the active workbook

function TextToCell1() {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.appendRow(['Hello world', 'Agent 007', 'Today']);
}



write to a defined workbook to the defined Sheet

function WriteToSpreadsheet() {
   //ID Spreadsheet in Google 
   var ss = SpreadsheetApp.openById("1Y_hnVcR5FoP-_6VDsCv-aOqSAQ");
   var name = ss.getName();

   var sheet = ss.getSheetByName('Sheet1');
   var range = sheet.getRange(5,5);
   range.setValue('agent 007');
}




write cycle for 1-4

function WriteInFor() {

   var ss = SpreadsheetApp.openById("1Y_hnVcR5FoP-_6qSAQYw5tR40yb-VeRirCs");
   var name = ss.getName();  

   var sheet = ss.getSheetByName('Sheet1');
 
   for (var i = 1; i < 5; i++) {
      var range = sheet.getRange(8,i);
       range.setValue(i);
  }
}



write cycle for 1-4 including conditions

function WriteForAndIf() {
   var ss = SpreadsheetApp.openById("1Y_hnVcR5FoP-_6VDsCv-aOqSAQYw5tR40yb-VeRirCs");
   var name = ss.getName();  

   var sheet = ss.getSheetByName('Sheet1'); 
 
    for (var i = 1; i < 5; i++) {
        var range = sheet.getRange(8,i);
         range.setValue(i);
        if (i == 3) range.setValue("None");
   }
}

pondělí 23. března 2015

InputBox

How to use InputBox in script apps Google

Method

inputBox(prompt)
inputBox(prompt, buttons)
inputBox(title, prompt, buttons)

Buttons

OK                               Single “OK” button.
OK_CANCEL             “OK” and a “Cancel” button.
YES_NO                      “Yes” and “No” button.
YES_NO_CANCEL     “Yes”, “No” and “Cancel” button.

Example 1

var name = Browser.inputBox('Enter your name'); // CZ Zadej své jméno
 
function myFunction() {
  var name = Browser.inputBox('Enter your name');
} 
 
InputBox google apps










Example 2

function myFunction() {
  var name = Browser.inputBox('Enter your name' , Browser.Buttons.YES_NO);
}


  











Example 3

function myFunction() {
  var name = Browser.inputBox('First Input Box' ,'Enter your name' , Browser.Buttons.YES_NO);
}


 

MsgBox

How to MsgBox  - Google App Script

How to dialog in Google

Method

msgBox(prompt)
msgBox(prompt, buttons)
msgBox(title, prompt, buttons)

Buttons

OK                               Single “OK” button.
OK_CANCEL             “OK” and a “Cancel” button.
YES_NO                      “Yes” and “No” button.
YES_NO_CANCEL     “Yes”, “No” and “Cancel” button.

Examples 1

Browser.msgBox("hello world");

function myFunction() {
  Browser.msgBox( 'hello world');
}






 

 

 

 

 

Examples 2

function myFunction() {
    Browser.msgBox( 'hello world' , Browser.Buttons.YES_NO);
}













Example 3


function myFunction() {
    Browser.msgBox('First Dialog Box' , 'hello world' , Browser.Buttons.YES_NO);
}