Created
September 3, 2022 13:13
-
-
Save rashgaroth/53c596eac3cfc9117faf71a69c9382d5 to your computer and use it in GitHub Desktop.
Google appscript change timesheet to current month
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
const options = { | |
weekday: 'short', | |
year: 'numeric', | |
month: '2-digit', | |
day: 'numeric' | |
} | |
const getTotalDaysInMonth = (year, month = new Date().getMonth()) => { | |
const _mnth = new Date(year, month, 0).getMonth() + 1 | |
const monthNumberArray = [_mnth - 1, _mnth, _mnth === 12 ? 1 : _mnth + 1] | |
let arr = [] | |
for (let i = 0; i < monthNumberArray.length; i++) { | |
arr.push({ | |
totalDays: new Date(year, monthNumberArray[i], 0).getDate(), | |
month: monthNumberArray[i] | |
}) | |
} | |
return arr | |
} | |
const monthArray = [['January', 'Jan'],['February', 'Feb'],['March', 'Mar'],['April', 'Apr'],['May', 'May'],['June', 'Jun'],['July', 'Jul'],['August', 'Aug'], | |
['September', 'Sep'],['October', 'Oct'],['November', 'Nov'],['December', 'Dec']] | |
const dayInWeeks = ['Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun'] | |
function onOpen(){ | |
const ui = SpreadsheetApp.getUi() | |
ui.createMenu('Change month') | |
.addItem('Change month now', 'changeSheetToMonthNow') | |
.addToUi() | |
} | |
function changeSheetToMonthNow() { | |
const ss = SpreadsheetApp.getActiveSheet() | |
const date = new Date() | |
const thisMonth = date.getMonth() + 1 | |
const thisYear = date.getFullYear() | |
const getRowDate = ss.getSheetValues(4, 5, 5, 31)[0] | |
const getRowDay = ss.getSheetValues(5, 5, 5, 31)[0] | |
const getMonthString = ss.getSheetValues(2, 1, 1, 1)[0][0].split(' ')[5] | |
// get string time sheet month | |
const getCurrentMonthTimeSheet = monthArray.filter((x) => x[0] === getMonthString)[0] | |
// get month of the current timesheet | |
const getCurrentMonthTimeSheetNumber = monthArray.indexOf(monthArray.filter((x) => x[0] === getMonthString)[0]) + 1 | |
// get month now in string | |
const getActualMonthNow = monthArray[thisMonth - 1] | |
const getFirstDate = getRowDate[0] | |
const getLastDate = getRowDate[getRowDate.length - 1] | |
const getFirstDay = getRowDay[0] | |
const getLastDay = getRowDay[getRowDay.length - 1] | |
const getTotalDaysInThisMonth = getTotalDaysInMonth(thisYear, thisMonth)[1] | |
const getTotalDaysIntCurrentTimeSheet = getTotalDaysInMonth(thisYear, getCurrentMonthTimeSheetNumber)[1] | |
const dateOnThisMonth = [] | |
for (let i = parseInt(getLastDate) + 1; i <= getTotalDaysIntCurrentTimeSheet.totalDays; i++) { | |
dateOnThisMonth.push({date: i, month: getTotalDaysIntCurrentTimeSheet.month}) | |
} | |
for (let i = 1; i <= getTotalDaysInThisMonth.totalDays; i++) { | |
dateOnThisMonth.push({date: i, month: getTotalDaysInThisMonth.month}) | |
if (dateOnThisMonth.length === getRowDate.length){ | |
break; | |
} | |
} | |
for (let i = 0; i < dateOnThisMonth.length; i++) { | |
const _getCurDate = new Date(thisYear, dateOnThisMonth[i].month - 1, dateOnThisMonth[i].date) | |
const _day = dayInWeeks[_getCurDate.getDay()] | |
dateOnThisMonth[i].day = _day | |
} | |
const dateValues = dateOnThisMonth.map((x) => x.day) | |
let str = `` | |
if (thisMonth !== 1) { | |
str = `${dateOnThisMonth[0].date} ${monthArray[thisMonth - 2][0]} ${thisYear} to ${dateOnThisMonth[dateOnThisMonth.length - 1].date} ${monthArray[thisMonth - 1][0]} ${thisYear}` | |
} else { | |
str = `${dateOnThisMonth[0].date} ${monthArray[11][0]} ${thisYear} to ${dateOnThisMonth[dateOnThisMonth.length - 1].date} ${monthArray[12][0]} ${thisYear}` | |
} | |
// change all | |
ss.getRange(5, 5, 1, getRowDate.length).setValues([dateValues]) | |
ss.getRange('A2:AP2').setValue(str) | |
if (thisMonth !== 1) { | |
SpreadsheetApp.getActiveSpreadsheet().rename(`Timesheet Timas - ${dateOnThisMonth[0].date} ${monthArray[thisMonth - 2][1]} ${thisYear} - ${dateOnThisMonth [dateOnThisMonth.length - 1].date} ${monthArray[thisMonth - 1][1]} ${thisYear}_Imam Syafei`) | |
} else { | |
SpreadsheetApp.getActiveSpreadsheet().rename(`Timesheet Timas - ${dateOnThisMonth[0].date} ${monthArray[11][0]} ${thisYear} - ${dateOnThisMonth[dateOnThisMonth.length - 1].date} ${monthArray[12][0]} ${thisYear}_Imam Syafei`) | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment