CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsspreadsheet
Kirill Konovalov
Kirill Konovalov

Reputation: 59

Script to update calculations in Google Sheets and save results from the previous period of time in a row

I have a chart that calculates the number of bugs that been logged in Customer Support together with a status that looks like this:

Actual Chart Screenshot

I use the following script to copy and paste the row from (today) in the row below to be able to track the number of solved/unresolved bugs through the time:

// global 
var ss = SpreadsheetApp.getActive().getSheetByName("Unresolved_Count");

function onOpen() {
  var menu = [{name:"Add New Last Row", functionName:"addRow"}];
  ss.addMenu("Extra", menu);
}

function addRow() {
  var sh = SpreadsheetApp.getActive().getSheetByName("Unresolved_Count"), lRow = sh.getLastRow(); 
  var lCol = sh.getLastColumn(), range = sh.getRange(lRow,1,1,lCol);
  sh.insertRowsAfter(lRow, 1);
  range.copyTo(sh.getRange(lRow+1, 1, 1, lCol), {contentsOnly:true});
}

My desirable outcome should do the following:

  1. Every day the script makes a snapshot of solved, unresolved and total number of cases and adds another row to the spreadsheet.

  2. The date in the first row calculated using (now) method. To preserve the date from updating, script copies values only.

My desirable result looks like this:

Chart with desirable Result

Unfortunately, as a result, I keep getting the same date on every row:

Chart with the result

I am not sure what I am doing wrong and would like to kindly ask you for an expert opinion. Please see the sheet "Unresolved Cases" for further reference.

Thanks!

Upvotes: 1

Views: 170

Answers (1)

Diego
Diego

Reputation: 9571

You're not currently copying the first row. The way you've defined range (sh.getRange(lRow,1,1,lCol)), it represents the last row, which is not the formula row.

Try this instead

function onOpen() {
  SpreadsheetApp.getUi()
  .createMenu("Extra")
  .addItem("Add New Last Row", "addRow")
  .addToUi();
}

function addRow() {
  var sh = SpreadsheetApp.getActive().getSheetByName("Unresolved_Count");
  var lRow = sh.getLastRow(); 
  var lCol = sh.getLastColumn();
  var range = sh.getRange(2,1,1,lCol);
  sh.insertRowsAfter(lRow, 1);
  range.copyTo(sh.getRange(lRow+1, 1, 1, lCol), {contentsOnly:true});
}

Upvotes: 1

Related Questions