Are you over 18 and want to see adult content?
More Annotations
A complete backup of egardwatches.com
Are you over 18 and want to see adult content?
A complete backup of adamdeane.wordpress.com
Are you over 18 and want to see adult content?
A complete backup of tallplayer6w7.tumblr.com
Are you over 18 and want to see adult content?
A complete backup of koenigsgalerie.de
Are you over 18 and want to see adult content?
A complete backup of vetrinarossa.com
Are you over 18 and want to see adult content?
A complete backup of shudaisdrybones.tumblr.com
Are you over 18 and want to see adult content?
A complete backup of insideamashtun.blogspot.com
Are you over 18 and want to see adult content?
A complete backup of olharastronomico.blogspot.com
Are you over 18 and want to see adult content?
Favourite Annotations
A complete backup of https://agronegocios.es
Are you over 18 and want to see adult content?
A complete backup of https://tweaking4all.com
Are you over 18 and want to see adult content?
A complete backup of https://empea.org
Are you over 18 and want to see adult content?
A complete backup of https://vapingunderground.com
Are you over 18 and want to see adult content?
A complete backup of https://gadgetinfinity.com
Are you over 18 and want to see adult content?
A complete backup of https://gachxinh.com
Are you over 18 and want to see adult content?
A complete backup of https://cfbnj.org
Are you over 18 and want to see adult content?
A complete backup of https://cityofpa.us
Are you over 18 and want to see adult content?
A complete backup of https://stellaadler.com
Are you over 18 and want to see adult content?
A complete backup of https://fanbyte.com
Are you over 18 and want to see adult content?
A complete backup of https://traicy.com
Are you over 18 and want to see adult content?
Text
since
HOW TO RESET A GOOGLE DOCS SPREADSHEET BY SCRIPT Using the Google API to script your spreadsheets (or any other document) is a really common operation but can be difficult for non programmers. In this post you will learn to : Clear a spreadsheet on Google Docs in parts or entirely, by triggering it manually or automatically at a given time. Send email notificationsBASTIEN LARGEPRET
Using the Google API to script your spreadsheets (or any other document) is a really common operation but can be difficult for nonprogrammers.
MARCH | 2013 | BASTIEN LARGEPRET 1 post published by blargepret during March 2013 ABOUT ME | BASTIEN LARGEPRET About me. I’m an internship student at Microsoft Innovation Center Belgium after a BS in “Informatics & Systems” at HELHa. As far a I remember, I’ve always been interested in science and technologies, even as a child. Particularly in programming that I passionately dosince
HOW TO RESET A GOOGLE DOCS SPREADSHEET BY SCRIPT Using the Google API to script your spreadsheets (or any other document) is a really common operation but can be difficult for non programmers. In this post you will learn to : Clear a spreadsheet on Google Docs in parts or entirely, by triggering it manually or automatically at a given time. Send email notificationsBASTIEN LARGEPRET
Using the Google API to script your spreadsheets (or any other document) is a really common operation but can be difficult for nonprogrammers.
MARCH | 2013 | BASTIEN LARGEPRET 1 post published by blargepret during March 2013 ABOUT ME | BASTIEN LARGEPRET About me. I’m an internship student at Microsoft Innovation Center Belgium after a BS in “Informatics & Systems” at HELHa. As far a I remember, I’ve always been interested in science and technologies, even as a child. Particularly in programming that I passionately dosince
HOW TO RESET A GOOGLE DOCS SPREADSHEET BY SCRIPT Using the Google API to script your spreadsheets (or any other document) is a really common operation but can be difficult for non programmers. In this post you will learn to : Clear a spreadsheet on Google Docs in parts or entirely, by triggering it manually or automatically at a given time. Send email notificationsBASTIEN LARGEPRET
Using the Google API to script your spreadsheets (or any other document) is a really common operation but can be difficult for nonprogrammers.
MARCH | 2013 | BASTIEN LARGEPRET 1 post published by blargepret during March 2013 ABOUT ME | BASTIEN LARGEPRET About me. I’m an internship student at Microsoft Innovation Center Belgium after a BS in “Informatics & Systems” at HELHa. As far a I remember, I’ve always been interested in science and technologies, even as a child. Particularly in programming that I passionately dosince
HOW TO RESET A GOOGLE DOCS SPREADSHEET BY SCRIPT Using the Google API to script your spreadsheets (or any other document) is a really common operation but can be difficult for non programmers. In this post you will learn to : Clear a spreadsheet on Google Docs in parts or entirely, by triggering it manually or automatically at a given time. Send email notificationsBASTIEN LARGEPRET
Using the Google API to script your spreadsheets (or any other document) is a really common operation but can be difficult for nonprogrammers.
MARCH | 2013 | BASTIEN LARGEPRET 1 post published by blargepret during March 2013 ABOUT ME | BASTIEN LARGEPRET About me. I’m an internship student at Microsoft Innovation Center Belgium after a BS in “Informatics & Systems” at HELHa. As far a I remember, I’ve always been interested in science and technologies, even as a child. Particularly in programming that I passionately dosince
HOW TO RESET A GOOGLE DOCS SPREADSHEET BY SCRIPT Using the Google API to script your spreadsheets (or any other document) is a really common operation but can be difficult for non programmers. In this post you will learn to : Clear a spreadsheet on Google Docs in parts or entirely, by triggering it manually or automatically at a given time. Send email notificationsBASTIEN LARGEPRET
Using the Google API to script your spreadsheets (or any other document) is a really common operation but can be difficult for nonprogrammers.
MARCH | 2013 | BASTIEN LARGEPRET 1 post published by blargepret during March 2013 ABOUT ME | BASTIEN LARGEPRET About me. I’m an internship student at Microsoft Innovation Center Belgium after a BS in “Informatics & Systems” at HELHa. As far a I remember, I’ve always been interested in science and technologies, even as a child. Particularly in programming that I passionately dosince
HOW TO RESET A GOOGLE DOCS SPREADSHEET BY SCRIPT Using the Google API to script your spreadsheets (or any other document) is a really common operation but can be difficult for non programmers. In this post you will learn to : Clear a spreadsheet on Google Docs in parts or entirely, by triggering it manually or automatically at a given time. Send email notificationsBASTIEN LARGEPRET
Using the Google API to script your spreadsheets (or any other document) is a really common operation but can be difficult for nonprogrammers.
MARCH | 2013 | BASTIEN LARGEPRET 1 post published by blargepret during March 2013 ABOUT ME | BASTIEN LARGEPRET About me. I’m an internship student at Microsoft Innovation Center Belgium after a BS in “Informatics & Systems” at HELHa. As far a I remember, I’ve always been interested in science and technologies, even as a child. Particularly in programming that I passionately dosince
HOW TO RESET A GOOGLE DOCS SPREADSHEET BY SCRIPT Using the Google API to script your spreadsheets (or any other document) is a really common operation but can be difficult for non programmers. In this post you will learn to : Clear a spreadsheet on Google Docs in parts or entirely, by triggering it manually or automatically at a given time. Send email notificationsBASTIEN LARGEPRET
Using the Google API to script your spreadsheets (or any other document) is a really common operation but can be difficult for nonprogrammers.
MARCH | 2013 | BASTIEN LARGEPRET 1 post published by blargepret during March 2013 ABOUT ME | BASTIEN LARGEPRET About me. I’m an internship student at Microsoft Innovation Center Belgium after a BS in “Informatics & Systems” at HELHa. As far a I remember, I’ve always been interested in science and technologies, even as a child. Particularly in programming that I passionately dosince
HOW TO RESET A GOOGLE DOCS SPREADSHEET BY SCRIPT Using the Google API to script your spreadsheets (or any other document) is a really common operation but can be difficult for non programmers. In this post you will learn to : Clear a spreadsheet on Google Docs in parts or entirely, by triggering it manually or automatically at a given time. Send email notificationsBASTIEN LARGEPRET
Using the Google API to script your spreadsheets (or any other document) is a really common operation but can be difficult for nonprogrammers.
MARCH | 2013 | BASTIEN LARGEPRET 1 post published by blargepret during March 2013 ABOUT ME | BASTIEN LARGEPRET About me. I’m an internship student at Microsoft Innovation Center Belgium after a BS in “Informatics & Systems” at HELHa. As far a I remember, I’ve always been interested in science and technologies, even as a child. Particularly in programming that I passionately dosince
HOW TO RESET A GOOGLE DOCS SPREADSHEET BY SCRIPT Using the Google API to script your spreadsheets (or any other document) is a really common operation but can be difficult for non programmers. In this post you will learn to : Clear a spreadsheet on Google Docs in parts or entirely, by triggering it manually or automatically at a given time. Send email notificationsBASTIEN LARGEPRET
Using the Google API to script your spreadsheets (or any other document) is a really common operation but can be difficult for nonprogrammers.
MARCH | 2013 | BASTIEN LARGEPRET 1 post published by blargepret during March 2013Skip to content
* Follow: RSS
BASTIEN LARGEPRET
a dev's blog
* Home
* About me
* Non classé
3 March 2013
HOW TO RESET A GOOGLE DOCS SPREADSHEET BY SCRIPT Using the Google API to script your spreadsheets (or any other document) is a really common operation but can be difficult for non programmers. In this post you will learn to : * Clear a spreadsheet on Google Docs in parts or entirely, by triggering it manually or automatically at a given time. * Send email notifications to people so they don’t forget to fillthe document again.
I started from a simple problem : at work, we order sandwiches via an Excel sheet on the intranet. The first people who wants to order sandwiches opens it, clears it and sends a reminder to everyone and, later, sends it by email to the sandwiches store. Let’s be honest : it is a real pain in the neck and sometimes, the person who has to send it forgets and nobody gets food. So I decided to automate the process. Of course, it can be used for any kind of order, time shifts, etc. The applications are limitless. Note that the script will be written in Javascript. THE PROJECT REQUIREMENTS To begin with, you need a Google Docs spreadsheet, for this post, I’ll use this one. Feel free to use your own, I’ll explain how toadapt my code.
I
know it’s in French but it doesn’t matter since the sheet’s content is not important for the script, you can replace it by whatever you want. There are 3 things to notice : * There’s a new menu on the top of the page. It’s added by the script and I’ll use it to manually trigger those actions : reset the sheet, send it and send a reminder. * I put all the information that doesn’t need to be reset on top of the sheet. It’s not mandatory but it will make your work easier. * I actually created 5 sheets. The first one contains the order. The second is static and contains the sandwiches list, you can ignore it. The third one contains an email addresses list. If you want to receive a daily reminder, just put your address in the list, if you want to stop receiving it, just remove your address. The fourth and fifth ones contains templates for the order and the reminder emails so they areeasy to edit.
_
The
reminder emails list.__
The
reminder template._
STEP 1 : CREATE A SCRIPT Click on the “Tools” menu > “Script manager” and you should see this. Yours will be empty at first, of course._
the
script manager_
_the
script editor_
When you create a new script, you will be asked to select the script’s type. Select “empty project”. You can also select “spreadsheet project”, it will insert some code samples but youdon’t need them.
Google offers you two very useful tools : the logs and the execution transcript. Both can be accessed in the “View” menu. The logs allow you to log any message or variable value during the script’s execution by simply calling : Logger.log("some message"); You can replace “some message” by any variable and its text representation will be logged. The execution transcript registers automatically all the function calls with the parameters values. Both are reset at each script execution. STEP 2 : THE RESET SCRIPT // Retrieves a sheet's range from its name function getRange(name) { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName(name); return sheet.getDataRange();}
// Resets all the data cells without changing the header// cells.
function resetSheet() { // Retrieving the values contained in the sheet. var range = getRange("Commande"); var values = range.getValues(); // Browsing the rows & cells to reset. for (var i = COMMAND_ROWS_OFFSET ; i < values.length ; i++) { for (var j = 0 ; j < values.length ; j++) { // Resetting the cell in the data copy.values = "";
}
}
// Synchronizing the (now empty) data copy and the sheet. range.setValues(values);}
A range is the smallest rectangle in a sheet that contains all the data. All the empty rows and columns will be ignored if they aren’t followed by an non-empty row or column. As there is one range per sheet, we also need to know the sheet’s name. You can see it in the sheets list, at the bottom-left of your spreadsheet. But a range is an object, to retrieve the data, you need to call its “getValues” method, it will return the range as an 2-dimensions array. This is not the real data but a copy of the data. So, once you browsed it and cleared all the cells, you’ll still need to replace the range’s values by the copy. It can be done by calling the range’s “setValues” method. But all the data doesn’t need to be cleared. The sheet’s first lines contain some static information, like who need to receive the command or the user instructions so you won’t browse all the range but skip the first lines. When computing the number of lines to skip at the top of the sheet, be aware that, if you merge cells, the result is only visual. They will be considered as separated cells in the range but only the first one of the merged cells will contain the data. STEP 3 : THE REMINDER SCRIPT // Loads an email template and sends it to the recipients function sendEmail(template, recipients) { // Retrieving the email's subject and content in the requested sheet. values = getRange(template).getValues(); var subject = values; var message = values; // Sending the email MailApp.sendEmail(recipients, subject, message);}
// Sends a reminder email to everybody who registered his email // address the 3rd (reminder) sheet. function sendReminder() { // Retrieving the recipients list in the 3rd (reminder) sheet. var values = getRange("Rappel").getValues(); var recipients = ""; for (var i = REMINDER_ROWS_OFFSET ; i < values.length ; i++){
if (i > REMINDER_ROWS_OFFSET) {recipients += ", ";
}
var row = values;
recipients += row;
}
// Sending the email sendEmail("Mail rappel", recipients);}
The “sendEmail” function loads the email template in the sheet named in the arguments and sends it by simply calling the “MailApp.sendEmail” function. You can send 500 emails a day with this method. To check how much remaining emails you have, use the “MailApp.getRemainingDailyQuota”. The limit of recipients peremail is 99.
The “sendReminder” function retrieves the email addresses list in the “reminder” sheet and formats it as a string that contains the addresses separated by a comma : it’s the email’s recipient. Then it calls the “sendEmail” function. STEP 4 : THE ORDER SCRIPT This script is like the previous one but the recipient and the email template are located elsewhere. // Sends an email that contains a link to the order function sendOrder() { // Retrieving the recipient in the main sheet var values = getRange("Commande").getValues(); var recipient = values; // Sending the email sendEmail("Mail commande", recipient);}
STEP 5 : TRIGGERING THE SCRIPTS If you add an “onOpen” function in your script, it will be executed each time the document is opened. You can use it to add a new menu in the spreadsheet’s UI that will allow you to manually trigger the scripts. The menu will be populated with the items of a 2-dimensions array. Each entry is composed of 2 items : the action’s name and the callback which is the name of the function that will be called when the user clicks it. You may have to wait a few seconds after opening the document before the new menu appears. // Adds a menu in the user interface when the spreadsheet// is opened.
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();var entries = ;
sheet.addMenu("Sandwichs", entries);};
SEPT 6 : AUTOMATION OF THE PROCESS. For the last step, you will set up the cron so the reset and the reminder scripts will be automatically executed at a given time. The order script could be automated too but I don’t recommend it since it will send a email to people you don’t know or work with. It’s always better to keep an human to do this. Anyway, it’s a one-click action and it shouldn’t take too much time. Click on the “Resources” menu > Current project’s triggers. You should see a list of the current automated triggers, add a new one. The “run” option allows you to choose the function that will be ran at a given event and the “events” options allows you to set up that event. There are 2 kinds of events : from the spreadsheet and time-driven. The first category of events are triggered by the spreadsheet. A good example is the “onOpen” function that is called when an user opens the document. An event can also be triggered when the document is edited or submitted. The second category of events happens at a given time or at a regular time interval. I selected “day timer” and “midnight to 1 am” for the reset script so it will happen by night when nobody uses it. Note that you cannot choose a specific time but a one-hour interval. I selected “7am to 8am” for the reminder script so it will happen just before my colleagues arrive at work. Don’t forget to check the time zone. You can choose to be notified by email immediately or with a daily report if the script execution fails.CONCLUSION
The Google API has very much more to offer than this but you learnt the basis. Now you can take a spreadsheet with any kind of content and automate some maintenance operations. I hope you found this littleguide useful.
REVISION
* 07/03/2013 : If you share the spreadsheet with the link or set it public, the people who aren’t connected with a Google account will not see the new menu.Advertisements
Report this ad
Advertisements
Report this ad
Leave a Comment
*
*
RECENT POSTS
* How to reset a Google Docs spreadsheet by script*
RECENT COMMENTS
*
ARCHIVES
* March 2013
*
CATEGORIES
* Non classé
*
META
* Register
* Log in
* Entries feed
* Comments feed
* WordPress.com
Advertisements
Report this ad
Blog at WordPress.com. Do Not Sell My Personal InformationPost to
Cancel
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use. To find out more, including how to control cookies, see here: CookiePolicy
* Follow
*
* Bastien Largepret
* Customize
* Follow
* Sign up
* Log in
* Report this content * Manage subscriptions* Collapse this bar
Report this ad
Details
Copyright © 2024 ArchiveBay.com. All rights reserved. Terms of Use | Privacy Policy | DMCA | 2021 | Feedback | Advertising | RSS 2.0