LoginSignup
0
0

More than 1 year has passed since last update.

GAS_スプレッドシートにて毎月情報をデフォルトにリセットさせるように

Last updated at Posted at 2021-08-07

表題のままなのですが、個人的に毎月の細々としたものをデフォルトでできるようにしました。

より良い書き方、関数の分け方あれば教えてください。

/** @OnlyCurrentDoc */
const CHECK_SHEET = 'check_sheet';
const FORMAT_SHEET = 'format';
const JST_DIFF = 9;
const DATE_FORMAT = 'YYYY年MM月';

const getMonthDate = (diff = 0) => {
    const now = dayjs.dayjs();
    return now.add(JST_DIFF, 'hour').add(diff, 'month');  
};

const backUp = () => {

  const spreadsheet = SpreadsheetApp.getActive();

  const lastMonth = getMonthDate(-1).format(DATE_FORMAT);

  spreadsheet.setActiveSheet(spreadsheet.getSheetByName(CHECK_SHEET), true);
  const sheet = spreadsheet.getSheetByName(lastMonth);

  if (sheet !== null){
      return
  }

  spreadsheet.duplicateActiveSheet();
  spreadsheet.moveActiveSheet(3);
  spreadsheet.getActiveSheet().setName(lastMonth);
};

// delete sheet
const deleteSheet = (spreadsheet) => {
  const sheet = spreadsheet.getSheetByName(CHECK_SHEET);

  if (sheet !== null){
    spreadsheet.deleteSheet(sheet);
  }
};


// make new
const makeNew = (spreadsheet) => {
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName(FORMAT_SHEET), true);
  spreadsheet.duplicateActiveSheet();
  spreadsheet.moveActiveSheet(1);
  spreadsheet.getActiveSheet().setName(CHECK_SHEET);
};

const updateDate = (spreadsheet) => {
  const sheet = spreadsheet.getSheetByName(CHECK_SHEET);
  const month = getMonthDate().format(DATE_FORMAT);

  sheet.getRange("B2").setValue(month);
}


// update sheet
const updateSheet = () => {
  const spreadsheet = SpreadsheetApp.getActive();
  backUp()
  deleteSheet(spreadsheet);
  makeNew(spreadsheet);
  updateDate(spreadsheet);
};```
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0