Skip to content

Instantly share code, notes, and snippets.

@rashgaroth
Created September 3, 2022 13:13
Show Gist options
  • Save rashgaroth/53c596eac3cfc9117faf71a69c9382d5 to your computer and use it in GitHub Desktop.
Save rashgaroth/53c596eac3cfc9117faf71a69c9382d5 to your computer and use it in GitHub Desktop.
Google appscript change timesheet to current month
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