How to automate monthly report creation and update by Google slide 〜after updating files, coping files automatically and regularly〜

GAS
この記事は約7分で読めます。
Advertisements

Target Users

  • who preparation monthly report by PPTX
  • who manage budget control by spreadsheet
  • who operate only copy&paste monthly spreadsheet data to PPTX
  • who read related article “how to automate monthly report creation and update by Google slide”

Outline: Google Slide automatically copying

In past post, I introduce how to use Google Slide to make monthly reporting easy.

See the previous article below.

This method was to update the data in slide with clicking linkage button. This method does not require you to customize for data linkage, which makes it easier. However, since the files themselves are not copied, leaving the log is the basic mission in the management department, so getting more efficiency want to save an automatic copy every month on a regular basis. So, this time the master file created in previous article will be automatically copied using GAS and installable trigger.

Gaol

image of files structure

Let’s do saving the report file for each month in the same folder hierarchy that the master file is located. The date of the last update is different because the data is updated / processed after saving, but the copy itself is executed on a regular basis using a trigger.

Actual Script

This code is very simple!! Further, can be implemented in a few lines. So, Let’s try it.

function targetFileCopy() {
  //to create file and set file_name, prepare some variables
  var date = new Date();
  var year = date.getFullYear();
  var month = date.getMonth() + 1;
  var file_name = year.toString() + "_" + month.toString()+ "MonthlyReport"; //on execution, use this file_name as setting file name
  
  //Copying file
  var originalFile = DriveApp.getFileById("input fileID of master file").makeCopy(file_name);
}

In order to update the file name, we set up the variable “file_name” with the name to be given at runtime and use “makeCopy” method in DriveApps objects. “makeCopy” method accepts 1. name to set (String Type) 2. folder location to save (Folder Type) as arguments. This time, the location of saving folder is the same as the master file, so no arguments are required, just the name is set.

Details are explained in the GAS Document, so I’ll add a link just in case.

Class File  |  Apps Script  |  Google Developers

How to set the installable trigger (for your reference)

Screen shot to set trigger

I don’t have any special explanations, but finish around 10 working days on a monthly basis, including the creation of a report, isn’t it? If you are not finished, we will help you to speed up the monthly settlement.

If you are done constantly, please have the setting of time-driven > month-based timer> 15 days> execution time, the trigger will be executed at the specified time of 15 days and the copy file will be created automatically.

Finally,

Once the data format is determined, updating the Slide is very easy. And more copying and saving can be automated with the above code and trigger settings. Of course, the data update includes the qualitative information part, so there is actually a part to manually modify the Master every month. However, if you automate the numerical management and the like, the work after that will be easier and easier.

I don’t think that mere numerical reporting is particularly valuable, so I think it’s better to spend time thinking about qualitative information and subsequent actions while decreasing such as worthless actions .

We want to be able to rely on the basic functions of GAS and Google to be able to automate and increase the time we can face the business.

関連記事: GoogleSlide自動更新機能

  1. Google slideを使って毎月のレポート作成・更新を自動化する方法
  2. Google slideを使って毎月のレポート作成・更新を自動化する方法〜更新処理編〜
  3. 続Google slideを使って毎月のレポート作成・更新を自動化する方法 〜更新自動化の後はファイル自体も定期コピーしよう〜

コメント