Google Apps Script: 2015

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

ú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');
}

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

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);
}

Stránky

sobota 4. dubna 2015

sobota 28. března 2015

Example 1

Example 2

Example 3

pátek 27. března 2015

First email for test

Email - send html code

čtvrtek 26. března 2015

Create custom menu

Create custom menu 2

Remove custom menu 2

if

if, else

if, if else, else

úterý 24. března 2015

For with Step 1

For with Step 2

For with countdown loop

Method

setBackgroundRGB(red, green, blue)

setBackground(color)

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

setDataValidation(rule)

setDataValidations(rules)

setFontColor(color)

setFontColors(colors)

setFontFamilies(fontFamilies)

setFontFamily(fontFamily)

setFontLine(fontLine)

setFontLines(fontLines)

setFontSize(size)

write to cells of the active workbook

write a new line in the active workbook

write to a defined workbook to the defined Sheet

write cycle for 1-4

write cycle for 1-4 including conditions

pondělí 23. března 2015

Method

Buttons

Example 1

Example 2

Example 3

How to MsgBox - Google App Script

Method

Buttons

Examples 1

Examples 2

Example 3