Showing posts with label Google Script. Show all posts
Showing posts with label Google Script. Show all posts
Google Drive Auditor - Who Can Access Your Files
The auditor creates a detailed report of permissions
and access settings of files in Google Drive
that are owned or shared with you.
Anonymize Ip Google Analytics
If you want not track the Ip with Google Analytics you can use the command following:
How to share a document from Google Drive
Suppose that you have create a file under Google Drive and you want read this file from your html page.
How do you do ?
File Permissions and Sharing Settings in Google Drive
If you want to know who has access to your files in Google Drive there is the following script by Amit Agarwal.:
/*
======================================
Who Can See Your Files in Google Drive
======================================
Written by Amit Agarwal on 09/11/2013
Tutorial :: http://labnol.org/?p=28237
*/
function ScanGoogleDrive() {
// Get all files in your Google Drive
var files = DriveApp.getFiles();
var msg = "";
var report = "";
while (files.hasNext()) {
try {
var file = files.next();
// Get the Sharing Permissions of a file
var access = file.getSharingAccess();
// Is a file privately shared with specific users
if (access == DriveApp.Access.PRIVATE) {
var viewers = file.getViewers();
var editors = file.getEditors();
if (editors.length || viewers.length) {
var view="", edit="";
// Find who has "view" permissions for that file
for (var i=0; i<viewers.length; i++) {
if (view.length) {
view += ", ";
}
view += viewers[i].getName() + " " + viewers[i].getEmail();
}
// Find who has "edit" permissions for that file
for (var i=0; i<editors.length; i++) {
if (edit.length) {
edit += ", ";
}
edit += editors[i].getName() + " " + editors[i].getEmail();
}
report += "<tr><td style='padding:5px;'><a href='" + file.getUrl() + "'>" + file.getName()
+ "</a></td><td style='padding:5px'>" + view + "</td><td style='padding:5px'>" + edit + "</td></tr>";
}
// Or is the file public, shared with anyone on the Internet
} else if ((access == DriveApp.Access.ANYONE_WITH_LINK) || (access == DriveApp.Access.PRIVATE)) {
var permission = file.getSharingPermission();
// Is the file shared with "Edit" permissions?
if (permission == DriveApp.Permission.EDIT) {
permission = "Public";
} else {
permission = "";
}
report += "<tr><td style='padding:5px'><a href='" + file.getUrl() + "'>" + file.getName()
+ "</a></td><td style='padding:5px'>Public</td><td style='padding:5px'>" + permission + "</td></tr>";
}
}
catch (e) {
Logger.log(e.toString());
}
}
// Send the report as HTML Email
report ="<table><tr><th'>File Name</th><th>Who can view</th><th>Who can edit</th></tr>" + report + "</table>";
MailApp.sendEmail(Session.getActiveUser().getEmail(),
"Google Drive - File Sharing Report", report, {htmlBody: report});
}
How to retrieve the draft e-mails from google account and send it
function draftMail() {
var threads = GmailApp.search('in:draft', 0, 10);
if (threads.length === 0) {
Browser.msgBox("No draft emails found");
return;
}
var myapp = UiApp.createApplication().setTitle('Select and Send Mail').setHeight(160).setWidth(300);
var top_panel = myapp.createFlowPanel();
top_panel.add(myapp.createLabel("Please select an email from your Drafts folder"));
var lb = myapp.createListBox(false).setWidth(250).setName('templates').addItem("Select draft...").setVisibleItemCount(1);
for (var i = 0; i < threads.length; i++) {
lb.addItem((i+1)+'- '+threads[i].getFirstMessageSubject().substr(0, 40));
}
top_panel.add(lb);
top_panel.add(myapp.createLabel("").setHeight(10));
top_panel.add(myapp.createLabel("").setHeight(5));
var ok_btn = myapp.createButton("Send Email");
top_panel.add(ok_btn);
myapp.add(top_panel);
var handler = myapp.createServerClickHandler('sendMail').addCallbackElement(lb);
ok_btn.addClickHandler(handler);
SpreadsheetApp.getActiveSpreadsheet().show(myapp);
}
function sendMail(e) {
try {
var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.toast("I'm sending the email, please wait...",'Gmail',-1);
var draft = GmailApp.search("in:drafts")[(parseInt(e.parameter.templates.substr(0, 2))-1)].getMessages()[0];
var body = draft.getPlainBody();
var to = draft.getTo();
var subject = draft.getSubject();
GmailApp.sendEmail(to, subject, body);
ss.toast('Your email is sent to ' + to);
} catch (e) {
ss.toast(e.toString());
}
var app = UiApp.getActiveApplication();
app.close();
return app;
}
JDBC with Google Drive
Google Apps Script has the ability to make connections to databases via JDBC with the Jdbc Service
The following code is an example that show how to connect vua JDBC:
The following code is an example that show how to connect vua JDBC:
// Script-as-app template.
function doGet()
{
var myapp = UiApp.createApplication().setTitle('Connect with DB via ODBC');
var mypanel = myapp.createVerticalPanel();
var myhost = myapp.createTextBox().setId('Host').setName('Host');
var myport = myapp.createTextBox().setId('Port').setName('Port');
var myistance = myapp.createTextBox().setId('Istance').setName('Istance');
var myuser = myapp.createTextBox().setId('User').setName('User');
var mypassword = myapp.createPasswordTextBox().setId('Password').setName('Password');
var mygrid = myapp.createGrid(5, 2);
mygrid.setWidget(0, 0, myapp.createLabel('Host:'));
mygrid.setWidget(0, 1, myhost);
mygrid.setWidget(1, 0, myapp.createLabel('Port:'));
mygrid.setWidget(1, 1, myport);
mygrid.setWidget(2, 0, myapp.createLabel('Istance'));
mygrid.setWidget(2, 1, myistance);
mygrid.setWidget(3, 0, myapp.createLabel('User'));
mygrid.setWidget(3, 1, myuser);
mygrid.setWidget(4, 0, myapp.createLabel('Password'));
mygrid.setWidget(4, 1, mypassword);
var mybutton = myapp.createButton('Try to connect');
var handler = myapp.createServerHandler('trytoconnect');
handler.addCallbackElement(myhost);
handler.addCallbackElement(myport);
handler.addCallbackElement(myistance);
handler.addCallbackElement(myuser);
handler.addCallbackElement(mypassword);
mybutton.addClickHandler(handler)
mypanel.add(mygrid);
mypanel.add(mybutton);
myapp.add(mypanel);
return myapp;
}
function trytoconnect(e)
{
var myapp = UiApp.getActiveApplication();
var myhost = e.parameter.Host;
var myport = e.parameter.Port;
var myistance = e.parameter.Istance;
var myuser = e.parameter.User;
var mypassword = e.parameter.Password;
Logger.log(myhost);
Logger.log(myport);
Logger.log(myistance);
Logger.log(myuser);
Logger.log(mypassword);
var conn = Jdbc.getConnection("jdbc:mysql://"+myhost+":"+myport+"/"+myistance, myuser, mypassword);
Logger.log(conn);
conn.close();
myapp.close();
return myapp;
}
ScriptDb with Google Drive
ScriptDb is a JavaScript Object database for Google Apps Script.
In this example you can insert, show, delete and search the data with ScriptDB with Google Drive:
In this example you can insert, show, delete and search the data with ScriptDB with Google Drive:
// Script-as-app template.
function doGet()
{
var myapp = UiApp.createApplication().setTitle('Connect with DB');
var mypanel = myapp.createVerticalPanel();
var myname = myapp.createTextBox().setId('Name').setName('Name');
var myage = myapp.createTextBox().setId('Age').setName('Age');
var mycity = myapp.createTextBox().setId('City').setName('City');
var mygrid = myapp.createGrid(3, 2);
mygrid.setWidget(0, 0, myapp.createLabel('Name:'));
mygrid.setWidget(0, 1, myname);
mygrid.setWidget(1, 0, myapp.createLabel('Age:'));
mygrid.setWidget(1, 1, myage);
mygrid.setWidget(2, 0, myapp.createLabel('City'));
mygrid.setWidget(2, 1, mycity);
var mybutton = myapp.createButton('Insert record');
var handler = myapp.createServerHandler('insertData');
handler.addCallbackElement(myname);
handler.addCallbackElement(myage);
handler.addCallbackElement(mycity);
mybutton.addClickHandler(handler)
var showData = myapp.createButton('Show all data stored');
var handler = myapp.createServerHandler('showData');
showData.addClickHandler(handler)
var clearData = myapp.createButton('Delete all data of database');
var handler = myapp.createServerHandler('clearData');
clearData.addClickHandler(handler)
var searchData = myapp.createButton('Search record');
var handler = myapp.createServerHandler('searchData');
handler.addCallbackElement(myname);
handler.addCallbackElement(myage);
handler.addCallbackElement(mycity);
searchData.addClickHandler(handler)
var textArea = myapp.createTextArea().setId('mylog').setName('mylog').setWidth('500').setHeight('200');
mypanel.add(mygrid);
mypanel.add(mybutton);
mypanel.add(showData);
mypanel.add(clearData);
mypanel.add(searchData);
mypanel.add(textArea);
myapp.add(mypanel);
return myapp;
}
function insertData(e) {
var myapp = UiApp.getActiveApplication();
var name = e.parameter.Name;
var age = e.parameter.Age;
var city = e.parameter.City;
var db = ScriptDb.getMyDb();
var ob = {type: "mytable",
name: {first: name, age:age},
address: {city: city}
};
var result = db.save(ob);
showData(null);
myapp.close();
return myapp;
}
function showData(e) {
var myapp = UiApp.getActiveApplication();
var db = ScriptDb.getMyDb();
var results = db.query({});
myapp.getElementById('mylog').setText("");
var msg = "";
while (results.hasNext()) {
var result = results.next();
msg = msg + "type: " + result.type + ", name: " + result.name.first + ", age: " + result.name.age + ", city: " + result.address.city + "\n";
}
myapp.getElementById('mylog').setText(msg);
myapp.close();
return myapp;
}
function clearData(e) {
var myapp = UiApp.getActiveApplication();
var db = ScriptDb.getMyDb();
while (true)
{
var result = db.query({});
if (result.getSize() == 0) {
break;
}
while (result.hasNext()) {
db.remove(result.next());
}
}
showData(null);
myapp.close();
return myapp;
}
function searchData(e) {
var myapp = UiApp.getActiveApplication();
var name = e.parameter.Name;
var age = e.parameter.Age;
var city = e.parameter.City;
var db = ScriptDb.getMyDb();
var obage = (age == "") ? {} : {age: age} ;
var obname = ( name == "" ) ? obage : {first: name, age: obage} ;
var obcity = (city == "") ? {} : {city: city};
var ob = {type: "mytable", name: obname, address: obcity };
Logger.log(ob);
var stored = db.query(ob);
var msg = "";
while (stored.hasNext())
{
var result = stored.next();
msg = msg + "type: " + result.type + ", name: " + result.name.first + ", age: " + result.name.age + ", city: " + result.address.city + "\n";
}
myapp.getElementById('mylog').setText(msg);
myapp.close();
return myapp;
}
Why Google Drive doesn't work via web
If you try to load the Google Drive via web and you don't see the files and the page is blocked then there is only one the solution. The problem is the Kaspersky anti-virus that block this page; the solution is disable the "Parent Control":
Show the contents of Google Docs in tree format
The following code demonstrate use of Google Apps Script UI Services and DocList Services to display contents of a Google Docs Collection in Tree format.
The ouput will be the following:
function doGet(e){
var app = UiApp.createApplication();
var scrollPanel = app.createScrollPanel(); // Scroll Panel is a Google Web Toolkit Widget
tree = app.createTree(); // Tree is a Google Web Toolkit Widget
tree.addItem(buildTree(app, "")); // <== you can put a filter or a specific directory
scrollPanel.add(tree);
scrollPanel.setHeight("500");
app.add(scrollPanel);
return app;
}
function buildTree(a, searchTerm){
var tree = a.createTreeItem(); // TreeItem is Google Web Toolkit Widget
tree.setText(searchTerm);
// Use of the Google Apps Script DocList Service to retrieve the collections.
var folders = DocsList.getFolder(searchTerm).getFolders();
for (var i = 0; i < folders.length; i++)
tree.addItem(buildTree(a, folders[i].getName())).setState(true, true);
var files = DocsList.getFolder(searchTerm).getFiles();
for (var i = 0; i < files.length; i++) {
if (files[i].getType() == "document") {
urlBase = "https://docs.google.com/document/edit?id=";
iconHTML = "https://docs.google.com/images/doclist/icon_7_document_list.png";
}
else if (files[i].getType() == "spreadsheet") {
urlBase = "https://spreadsheets.google.com/ccc?key=";
iconHTML = "https://docs.google.com/images/doclist/icon_7_spreadsheet_list.png";
}
else if (files[i].getType() == "presentation") {
urlBase = "https://docs.google.com/present/edit?id=";
iconHTML = "https://docs.google.com/images/doclist/icon_7_presentation_list.png";
}
else if (files[i].getType() == "drawing") {
urlBase = "https://docs.google.com/drawings/edit?id=";
iconHTML = "https://docs.google.com/images/doclist/icon_7_drawing_list.png";
}
else {
urlBase = "https://docs.google.com/fileview?id=";
iconHTML = "https://docs.google.com/images/doclist/icon_7_generic_list.png";
}
var image = a.createImage(iconHTML);
var fileLabel = a.createAnchor(files[i].getName(), urlBase+ files[i].getId());
var fileLabelPanel = a.createHorizontalPanel();
fileLabelPanel.add(image);
fileLabelPanel.add(fileLabel);
tree.addItem(fileLabelPanel).setState(true, true);
}
return tree;
}
The ouput will be the following:
File upload with google script
function doGet(e) {
var app = UiApp.createApplication().setTitle('File Upload');
var infoBox = app.createLabel().setVisible(false).setId('infoBox');
app.add(infoBox);
//Form panel is required for upload
var form = app.createFormPanel().setId('form').setEncoding('multipart/form-data');
app.add(form);
//a grid to hold the widgets to guild user
var formContent = app.createGrid().resize(4,2);
form.add(formContent);
//create a text box to name the file
formContent.setText(0, 0, 'File Name: ')
formContent.setWidget(0, 1, app.createTextBox().setName('newName'));
//create file upload widget
formContent.setWidget(1, 0, app.createLabel('File:'));
fileUploadBox = app.createFileUpload().setName("file");
formContent.setWidget(1, 1, fileUploadBox);
//create submit button
formContent.setWidget(2, 1, app.createSubmitButton('Upload'));
return app;
}
function doPost(e){
var app = UiApp.getActiveApplication();
//creates the file in Docs
var file = DocsList.createFile(e.parameter.file);
file.rename(e.parameter.newName);
//notify user of success
app.getElementById('infoBox').setText('Upload Complete').setVisible(true);
return app;
}
Make a menu with a submenu
function doGet(e)
{
var app = UiApp.createApplication();
var fooMenu = app.createMenuBar().setId('menu');
var fooMenuVertical = app.createMenuBar(true);
var handlerEdit = app.createServerClickHandler('showEdit');
var handlerView = app.createServerClickHandler('showView');
var handlerFile1 = app.createServerClickHandler('showFile1');
var handlerFile2 = app.createServerClickHandler('showFile2');
var handlerFile3 = app.createServerClickHandler('showFile3');
fooMenuVertical.addItem('SubFile1', handlerFile1)
fooMenuVertical.addItem('SubFile2', handlerFile2);
fooMenuVertical.addItem('SubFile3', handlerFile3);
fooMenu.addItem('File', fooMenuVertical)
fooMenu.addItem('Edit', handlerEdit);
fooMenu.addItem('View', handlerView).setId('prova');
fooMenu.addSeparator();
var label = app.createLabel('Item: ').setId('myLabel');
app.add(fooMenu);
app.add(label);
return app;
}
function showEdit(e)
{
var app = UiApp.getActiveApplication();
app.getElementById('myLabel').setText('Edit');
return app;
};
function showView(e)
{
var app = UiApp.getActiveApplication();
app.getElementById('myLabel').setText('View');
return app;
};
function showFile1(e)
{
var app = UiApp.getActiveApplication();
app.getElementById('myLabel').setText('File1');
return app;
};
function showFile2(e)
{
var app = UiApp.getActiveApplication();
app.getElementById('myLabel').setText('File2');
return app;
};
function showFile3(e)
{
var app = UiApp.getActiveApplication();
app.getElementById('myLabel').setText('File3');
return app;
};
A small google translator
This example show how to use the textArea and retrieve the content when I submit the value.
1: function doGet() {
2: var app = UiApp.createApplication();
3: //create a penel which will hold all the elements
4: var panel = app.createVerticalPanel();
5: var label1 = app.createLabel('Write the text to translate');
6: //Create text area which will hold the source text
7: var textArea1 = app.createTextArea().setId('originText').setName('originText').setWidth('300').setHeight('100');
8: textArea1.setText('Roma è la capitale d\'Italia');
9: //Create a button
10: var button = app.createButton('Translate');
11: var label2 = app.createLabel('Taranslated text:');
12: //Create text area which will hold translated text
13: var textArea2 = app.createTextArea().setId('destitinationText').setWidth('300').setHeight('100');
14: //Create a click handler which will call the translate function
15: var handler = app.createServerClickHandler('translate');
16: handler.addCallbackElement(textArea1); // This row is important
17: button.addClickHandler(handler);
18: //add all the elemnts to the panel
19: panel.add(label1).add(textArea1).add(button).add(label2).add(textArea2);
20: //Add the panel to the application
21: app.add(panel);
22: return app;
23: }
24: function translate(e){
25: //Get the current activae application
26: var app = UiApp.getActiveApplication();
27: //get the source text of TextArea1
28: var text = e.parameter.originText;
29: //Now translate the text
30: var translatedText = LanguageApp.translate(text, 'it', 'en');
31: //set the text of text area 2 as the translated text
32: app.getElementById('destitinationText').setText(translatedText);
33: return app;
34: }
Retrieve and fill a document with your google contacts using Google Scripts
function myFunction() {
// Create a new document
var doc = DocumentApp.create('MyContactsList');
//loop all contacts and put them inside the document
var contacts = ContactsApp.getContacts();
for (var i=0; i<contacts.length; i++)
{
if ( contacts[i].getFullName() != '' )
{
try
{
doc.appendParagraph(contacts[i].getFullName() + ' ' +
contacts[i].getEmails()[0].getAddress() );
}
catch(e)
{
doc.appendParagraph(contacts[i].getFullName() + ' without email' );
}
}
}
// Save and close the document
doc.saveAndClose();
// Get the URL of the document
var url = doc.getUrl();
// Get the email address of the active user - that's you
var emailAddress = Session.getActiveUser().getEmail();
GmailApp.sendEmail(emailAddress,'Your contact list',
'The document filled with your contacts is here: ' + url);
}
Subscribe to:
Posts (Atom)