Google script to generate expense spreadsheet in Google Sheets

Google has started scripting support for all its products. So Google docs now support scripting which will allow end users to automate routine tasks. Google Scripts is a Javascript cloud scripting language (as opposed to Javascript being client-side only) which can make life easier for most of us (Or at least the tech-savvy ones). But if you are not a programmer, don't despair -- this tutorial does not assume coding experience but the ability to copy and paste in the right places.

Now all Google products support scripting and this includes Google Maps. So for example, if you want to write a script which will import the restaurant data from Google Maps, then make it into a list in Google Sheets, and then sort the list by distance from home and email that list to colleagues, it is now possible to achieve all of this due to the support of Google Scripts. This means that now many things can be automated and the end result is an increased level of productivity.

Let's say, we have an expense spreadsheet of my monthly expenses which we want to erase every month and start from scratch. This means I had to manually delete all the data and the notes and align the dates with the days again (my spreadsheet is broken into weeks running from Sun-Sat and does not go from 1-31 of the month, which would have been easier but not insightful). We can write a Google Script to automate this mundane task. The script had to figure out the dates when each weekend and then append a row of Weekly Total and at the end of the month there is some days extra which needs to be summed again for a Final Total. There are four categories - Grocery, Gas, Restaurant, Misc.). For a customized set of categories, another script needs to be written.

In order to run the Google Script, open up the scripts browser and copy paste the code below. The Script Browser can be accessed from Tools -> Script Editor. The first time the script is run, Google will ask for authorization. Once accepted you will need to run it again to actually execute it.

function generateExpenseSheet() {
  
  // written by Jones to create an expense spreadsheet v3.0 09/04/15 v2.0 08/12/15
  
  var sheet = SpreadsheetApp.getActiveSheet();

  var yesno = Browser.msgBox('Alert', 'Clear sheet?', Browser.Buttons.YES_NO);
  
  if (yesno === 'yes') {
    sheet.clear();
    sheet.clearNotes();
  } else {
    return;
  }
  
  sheet.clear();
  sheet.clearNotes();
  
  var weekday = ["Sun","Mon","Tue","Wed","Thu","Fri","Sat"];
  
// Create toady's date. Use it to extract the month and year info. Then use that info to create the first date of the month. 
// Then use getDay() method to get the day of the week on which this first date of the month falls.
  var d = new Date();
  var d2 = new Date(d.getFullYear(),d.getMonth(),1);
  var firstdayofmonth = d2.getDay();
  
// Create the last date of the month and then use it to extract the days of month.
// Using 0 in the day field of next month chooses the last day of previous month.
  var d1 = new Date(d.getFullYear(),d.getMonth()+1,0);
  var daysinMonth = d1.getDate();
  
//  Logger.log(firstdayofmonth);
  
  var currentrowno = 1;
  
// Fill column heading
  sheet.appendRow(['day', 'date','grocery','gas','restaurant','misc.','total','','TOTAL']);
  currentrowno++;
  
// start from Sun but fill with empty rows till first day of the month  
  
  for ( i = 0 ; i < firstdayofmonth ; i++,currentrowno++) 
    sheet.appendRow([weekday[(i)%7],'','','','','','' ]);
     
// start filling rows with info starting from first day of month  
  for ( j = 0 ; j < daysinMonth ; j++) {
    
    
    // after every one week then insert a 'TOTAL FOR THE WEEK' row
    if ( (j + firstdayofmonth) % 7 === 0) {    
      // generate SUM formula for each column 3-8, save the formulas in an array 
      var weekrange = [];
      for ( k = 0 ; k < 4 ; k++) {
        weekrange[k] = sheet.getRange(((j + firstdayofmonth)/7-1)*8+2, k+3,7,1).getA1Notation();
      }
      // insert formulas as a single row
      var rownoTotal = ((j + firstdayofmonth)/7 )*8 + 1 ;
      sheet.appendRow(['','','=SUM('+weekrange[0]+')','=SUM('+weekrange[1]+')','=SUM('+weekrange[2]+')', '=SUM('+weekrange[3]+')','=SUM(C'+ rownoTotal +':F'+ rownoTotal +')' ]);
      // make the sum row BOLDso that it is easy to differentiate from the other rows of the week
      sheet.getRange(currentrowno,3,1,5).setFontWeight("Bold");
 
      // increase row no after each appendRow
      currentrowno++;

    }
    
    // add blank rows with day of week and date and the total of the day
    var rowno =  (parseInt((j + firstdayofmonth)/7) +  1)*8 + 1 ;
    sheet.appendRow([weekday[(j + firstdayofmonth)%7],j+1,'','','','','=SUM(C'+ currentrowno +':F'+ currentrowno +')' ]);
   // increase row no after each appendRow
   currentrowno++;
    
  }
  
 // if there are extra days which are not totaled then total them
 // start at the row next to the last WEEKLY TOTAL ROW. find the date of that day. 
 // then subtract it from the total days in the month. that will give the row where the list ends. 
  var dateOfLastTotalRow = sheet.getRange((parseInt((j + firstdayofmonth)/7))*8,2).getValue();
  
  var rowNoOfLastTotalRow = (parseInt((j + firstdayofmonth)/7))*8 + 2 ;
  
 // generate SUM formula for each column 3-8, save the formulas in an array 
 for ( k = 0 ; k < 4 ; k++) {
   weekrange[k] = sheet.getRange(rowNoOfLastTotalRow, k+3,daysinMonth -  dateOfLastTotalRow,1).getA1Notation();
   }
  
  // get final row no. use it to calc. the sum at the lower right corner of the table and then use it in the MASTER TOTAL of the Month formula
  var finalRow = currentrowno;
  
  // insert SUM formulas as a single row in the FINAL ROW
   var sumrow = sheet.appendRow(['','','=SUM('+weekrange[0]+')','=SUM('+weekrange[1]+')','=SUM('+weekrange[2]+')','=SUM('+weekrange[3]+')','=SUM(C'+finalRow+':F'+finalRow+')' ]);
    sheet.getRange(currentrowno,3,1,5).setFontWeight("Bold");

 if ( finalRow == 41  )
  {
    var MasterSUM = '=G9+G17+G25+G33+G41';
    var grocerySUM = '=C9+C17+C25+C33+C41';
    var restaurantSUM = '=E9+E17+E25+E33+E41';
    var gasSUM = '=D9+D17+D25+D33+D41';
    var miscSUM = '=F9+F17+F25+F33+F41';
  }
  else if ( finalRow > 41 ) {
    var MasterSUM = '=G9+G17+G25+G33+G41+G'+finalRow;
    var grocerySUM = '=C9+C17+C25+C33+C41+C'+finalRow;
    var restaurantSUM = '=E9+E17+E25+E33+E41+E'+finalRow;
    var gasSUM = '=D9+D17+D25+D33+D41+D'+finalRow;
    var miscSUM = '=F9+F17+F25+F33+F41+F'+finalRow;
  }
 else 
  {
    var MasterSUM = '=G9+G17+G25+G33+G'+finalRow;
    var grocerySUM = '=C9+C17+C25+C33+C'+finalRow;
    var restaurantSUM = '=E9+E17+E25+E33+E'+finalRow;
    var gasSUM = '=D9+D17+D25+D33+D'+finalRow;
    var miscSUM = '=F9+F17+F25+F33+F'+finalRow;
  }
  
  sheet.getRange(1,10).setValue(MasterSUM);
  
  //make table for 
  sheet.getRange(5,9).setValue("Grocery");
  sheet.getRange(5,10).setValue(grocerySUM);
  
  sheet.getRange(6,9).setValue("Restaurant");
  sheet.getRange(6,10).setValue(restaurantSUM);

  sheet.getRange(7,9).setValue("Gas");
  sheet.getRange(7,10).setValue(gasSUM);

  sheet.getRange(8,9).setValue("Misc");
  sheet.getRange(8,10).setValue(miscSUM);
  
  // Make sure the cell is updated right away in case the script is interrupted
  SpreadsheetApp.flush();
 
}

Before running the script the user needs to save it under a project name. The choice of the name of the project doesn't matter at this stage so go ahead and name it what you wish to. Then click the small play button to run the script. When executing the script for the first time the user will receive an authorization window like this

Select Accept and run it again. Go back to your Google Sheet. There is a small message box waiting asking permission to clear the sheet. Click yes and watch the script generate the entire expense spreadsheet.

Need : Google Drive, Google Sheet, Google script (provided), Javascript knowledge (optional).