더 스마트하게, 더 열심히 일하기: 추가 개발자가 Project Development를 가속화하는 방법
빠르게 변화하고 끊임없이 진화하는 오늘날의 비즈니스 환경에서는 더 열심히 일하는 것이 아니라 더 스마트하게 일하는 것이 성공의 필수 요소입니다. 이는 특히 혁신에 대한 요구가 높은 IT 업계에서 더욱 그러합니다.
Nowadays, cloud solutions are pushing out desktop apps dedicated to office work. One of the biggest office suites is provided by Google and includes Documents, Spreadsheets and Presentations, so the majority of what an average office needs.
Imagine that your job is about supervising someone else’s work, which usually leads to writing some reports. You want to keep things standardized and generic, so you created a spreadsheet with the list of things you need to control and some cells for a comment. Let’s assume that you are responsible for the quality of a digital 제품. Then your Spreadsheet may look like this:
In the example above, you can see several things:
This sheet is called “Report generator”, because I’m going to show you how this document can evolve into a really useful tool, allowing you to assess the quality of someone’s work results.
If you want to quantify some results, probably the best option would be to use a scale, e.g. 1-10 points, or something along these lines.
Let’s create a new sheet in the same document, call it “Config” and define it as protected (or at least append a keylock emoji to its name). This sheet is only for keeping some predefined options, like the ones below:
Now you can put these values into some dropdowns in the Report generator sheet by using the Data validation feature. Here you can find some instructions on how to achieve this:
Having done that, you can easily convert ratings to numeric values by using the IF formula. Your spreadsheet will stay human-friendly and your conclusions will be quantified (so measurable and comparable) at the same time.
This document is neat, however, imagine what happens when the scope of your 감사 grows and you have hundreds of things to check. You can save an empty form template and copy it every time you need to make a new report, but switching between document lists and the spreadsheet is very annoying. This is the point at which you start thinking about some automation.
Before we start coding, let’s prepare some buttons for bulk actions. According to the example above:
Also, we will need one extra action to save a new file when the audit is done.
First, let’s expand our Report generator with the functional area containing buttons.
Then, by using Insert -> Drawing, let’s add our buttons, one by one.
Our sample document should look like this:
We’re ready to add some logic to this beautiful UI! It’s very easy to start – just choose Tools -> Script editor in the top menu and the browser will jump to a new card.
The editor’s interface is very intuitive. If you are familiar with any 코드 editor, you should easily rename your file or create a new one (File -> New -> Script file).
Google Apps Script is nothing more than a JavaScript’s cousin, so to say. If you know JS, you can achieve GS proficiency, too!
The very first step is to collect all possibly needed references in one place and give them some meaningful names. We’re starting with global variables by removing the default myFunction declaration and typing something more useful in the Code.gs 파일을 만듭니다.
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
// sheets references
var report = spreadsheet.getSheets()[0]
var config = spreadsheet.getSheets()[1]
The above code is quite self-explanatory, but to make it absolutely clear:
Now we can go further and grab some useful data from our spreadsheet.
// editable area (cells with dropdowns)
var dropdowns = report.getRange('C4:C13')
// cell with subtitle (feature name)
var featureName = report.getRange('B2').getValue()
// options from 'Config' sheet
// notice that you can pass a single cell to getRange() method
// and changing options doesn't affect your script's functionality
var values = {
unacceptable: config.getRange('A2').getValue(),
improvements_needed: config.getRange('A3').getValue(),
perfect: config.getRange('A4').getValue(),
empty: ''
};
The only thing left to code is the definition of functions we want to assign to our buttons.
// button functions
function allPerfect() {
dropdowns.setValue(values.perfect)
}
function allNeedImprovements() {
dropdowns.setValue(values.improvements_needed)
}
function allUnacceptable() {
dropdowns.setValue(values.unacceptable)
}
function clear() {
dropdowns.setValue(values.empty)
}
function copy() {
var copy = spreadsheet.copy(featureName + ' - report')
// Now we have copied two sheets with same values
// but changed names (with a "copy" suffix),
// in this case, we have to give them original names
// rename report sheet
copy.getSheets()[0].setName(report.getName())
// rename config sheet
copy.getSheets()[1].setName(config.getName())
}
그리고 copy() method will automatically create a new spreadsheet named (in our example) “User login form – report”, right next to the current one.
Assign our methods to the buttons created before. Choose Assign a script after a right click on the button…
…and type the method’s name, here it’s allPerfect
You don’t need to close your generator. Use your bulk actions to prepare the form for a new report, change the feature name and then save another copy!
Possibilities of Google Apps Script are so wide that discussing them all would take a whole series of articles. If you are interested in optimizing your office work, feel free to check out the official docs and make your sheets even smarter!
There are some really useful hacks out there, like this one 에서 xfanatical.com about exporting Google Spreadsheet to PDF.
The example described in this article is 여기. Hope you’ll love it. Have fun!