Google Apps Script

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

Stránky

sobota 4. dubna 2015

Pre-filled google form

sobota 28. března 2015

Create user DialogBox

Example 1

Example 2

Example 3

pátek 27. března 2015

Send Email - script apps

First email for test

Email - send html code

čtvrtek 26. března 2015

Custom menu (create, delete,...)

Create custom menu

Create custom menu 2

Remove custom menu 2

If Then Else

if

if, else

if, if else, else

úterý 24. března 2015

For next loop step up down

For with Step 1

For with Step 2

For with countdown loop

How to set design cell cells

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)