Hunt for finance actuals in the source sheet, process, re-format, and save to destination actuals sheet.

updateActuals.gs

var source_sheet_doc = SpreadsheetApp.openById("XXXXX");
var destination_doc = SpreadsheetApp.openById("XXXXXX");

// How many months of actuals do we have?
// NOTE: Update this monthly as new actuals are available.
var months_of_actuals = 4;

// which column should we search for the trigger?
// NOTE: if the finance sheet changes, update this
var SEARCH_COLUMN_INDEX = 3;
    




// Add the "Update" menu to the spreadsheet containing options for updating data
// NOTE: You must manually call this function or add a trigger to run it on document load if you want it to run automatically
function addUi() {
  var ui = SpreadsheetApp.getUi();

  //add menu & items
  ui.createMenu('Update')
      .addItem('Update Finance Actuals', 'updateFinanceActuals')
      .addItem('Update Headcount Actuals', 'UpdateHeadcountActuals')
      .addItem('Update Finance & Headcount Actuals', 'updateBothHeadcountAndFinance')

  .addToUi();
}


// Run both sub-functions (finance & headcount)
function updateBothHeadcountAndFinance() {
  updateFinanceActuals();
  UpdateHeadcountActuals();
}


// Clear existing data from a given sheet
function emptyExistingSheet(sheetName) {
  var targetSheet = destination_doc.getSheetByName(sheetName);
  
  //only run if the sheet is non-empty (getRange returns an error if you end up using a negative row height)
  if (targetSheet.getLastRow()-1 != 0) {
    targetSheet.getRange(2, 1, targetSheet.getLastRow()-1, targetSheet.getLastColumn()).clearContent();
  }
}


function updateFinanceActuals() {
  var targetSheet = "finance actuals"
  emptyExistingSheet(targetSheet);
  var searchTrigger = "£"

  //Operations
  updateSheet("Operations","Operations Summary",7,20,targetSheet,searchTrigger);
  
  //Digital
  updateSheet("Digital","Digital Summary",5,19,targetSheet,searchTrigger);
 
  //Data
  updateSheet("Data","Data Summary",3,19,targetSheet,searchTrigger);
 
  //Tech
  updateSheet("Technology","Technology Summary",5,19, targetSheet, searchTrigger); 
  
}


function UpdateHeadcountActuals() {
  var targetSheet = "headcount actuals"
  emptyExistingSheet(targetSheet);
  var searchTrigger = "FTE"

  //Operations
  updateSheet("Operations","Operations Summary",7,20,targetSheet,searchTrigger);
  
  //Digital
  updateSheet("Digital","Digital Summary",5,19,targetSheet,searchTrigger);
 
  //Data
  updateSheet("Data","Data Summary",3,19,targetSheet,searchTrigger);
 
  //Tech
  updateSheet("Technology","Technology Summary",5,19, targetSheet, searchTrigger); 
  
}

function updateSheet(groupName, sourceSheetName, numberOfTeams,groupDateRowNum,destinationSheetName, searchTrigger) {
  // Had to redeclare this within this function as global assignment didn't work. Not sure why
  var source_sheet_doc = SpreadsheetApp.openById("1XAB4fybTxBVYVUi679yqQ8iUrRYavrZRkLDF9S3YNqE");

  var targetSheet = destination_doc.getSheetByName(destinationSheetName);
  
  var sourceSheet = source_sheet_doc.getSheetByName(sourceSheetName);
  
  var Source_Last_Row = sourceSheet.getLastRow();
  var Source_Last_Column = sourceSheet.getLastColumn();
  
  var array = new Array();
  
  // Bring all the data into a two dimensional array for faster processing
  var data = sourceSheet.getRange(1, 1, Source_Last_Row,Source_Last_Column).getValues();
  
  // loop through rows
  for (var row = groupDateRowNum-1; row<=Source_Last_Row-1 ; row++){
   // var thing = sourceSheet.getRange(row,4).getValue().match(/blah/i);
    
    if (data[row][SEARCH_COLUMN_INDEX].valueOf().indexOf(searchTrigger) > 0 && data[row][SEARCH_COLUMN_INDEX].valueOf().indexOf("TOTAL") < 0) {
      // move down one row, and loop through teams
      for (var teamRow = row+1; teamRow <= row+numberOfTeams ;teamRow++) {
      
        // loop through columns to get desired data
        for (var dateColumn = 10; dateColumn < 10 + months_of_actuals; dateColumn++) {
          
          // Remove extraneous asterisks added by finance (TODO: figure out why finanace put them there in the first place and if they're necessary)
          var Team = data[teamRow][SEARCH_COLUMN_INDEX].valueOf().replace("*", "").replace("**","");

          //use regular expression replacements to standardise types into our four categories ("CSs","Interims", "Interims - Services", "other_costs")
          var Type = data[row][SEARCH_COLUMN_INDEX].valueOf().replace(/CS.*/i,"CSs").replace(/.*services.*/i,"Interims - Services").replace(/^interims \(.*/i,"Interims").replace(/^other.*/i,"other_costs");
            
          var Date = data[groupDateRowNum-1][dateColumn];
          var Amount = data[teamRow][dateColumn];
          var CostCentre = data[teamRow][4];
          
          // create a temporary array for this row of values
          var Inputs = [groupName,Team,CostCentre,Type,Date,Amount];
          
          // push this row (as an array) ont our 2D array of values
          array.push(Inputs);
          
        }
      }
    }
  }
  
    // get latest filled row
    var Input_Row = targetSheet.getLastRow()+1
    
    // paste new batch of values from array into actual spreadsheet
    targetSheet.getRange(Input_Row,1,array.length,6).setValues(array);
}