Fill cells in one tab with specific values collected from other tabs in google sheets

Question

I'm trying to fill the cells range in one tab with the specific values collected from other tabs in my google sheets. However, I can't manage to write a correct script for it.

Here's a more detailed explanation:

I have several tabs in my google sheets document that represent a month of the year, i.e. they're named as July 2017, December 2016 and so on. Also I have two other tabs that are named Graphs and Blank.

Every month tab in its N6 cell has a value like this: Balance 33050,32 UAH (the number may vary from tab to tab).

What I'm trying to do:

I need to collect the N6 values from all my month tabs and fill this values into the A column of the Graphs tab. Then I need to build a graph out of all the values I've collected. But the main problem for now is collecting the values correctly.

I've tried to write a script for this, but it seems that I do something incorrectly. I'm quite new to the google script stuff, so maybe I'm missing something simple here.

Here's my script:

var months = [
  "january","february","march","april","may","june","july","august","september","october", "november","december"
];

var graphSheetName = 'Graphs';
var ss = SpreadsheetApp.getActiveSpreadsheet();
var graphSheet = ss.getSheetByName(graphSheetName);

// this function tells if the name of the tab refers to one of the month tabs.
function isMonthSheet(name) {
  return matchInArray(name, months);
}

function matchInArray(string, expressions) {
    var len = expressions.length,
        i = 0;

    for (; i < len; i++) {
        if (string.toLowerCase().match(expressions[i])) {
            return true;
        }
    }
    return false;
}

// This function runs on every edit of the google sheets document.
// It should fill the cells in the `Graphs` tab.
function getMonthsTotals() {  
  var sheets = ss.getSheets(); // get all tabs
  var values = [];

  for (var i = 0; i < sheets.length; ++i) {
   var sheet = sheets[i];

    // if it is a month tab, collect the N6 value into the `values` array.
    // "Balance 33050,32 UAH".slice(8,-4) --> "33050,32".
    if (isMonthSheet(sheet.getName())) {
      var value = sheet.getRange('N6').getValue().slice(8, -4);
      values.push(value);
    }
  }

  // fill the `Graphs` tab with the collected values into the `A` column.
  var rangeStr = 'A1:A' + values.length;
  graphSheet.getRange(rangeStr).setValues(values);
}

---

As I've already said above, I'm quite new to google sheets scripting, so I'm not very familiar with debugging in it as well, so my method is using the toast function. Hence, I can't really provide much of info as for how the script is actually running.

I seek advice and help on how to correct my script to make it run correctly.

Answer 1

function gatheringValues()
{
  var months=['January','February','March','April','May','June','July','August','September','October','December'];
  var ss=SpreadsheetApp.getActive();
  var allsheets=ss.getSheets();
  var n6s=[];
  for(var i=0;i<allsheets.length;i++)
  {
    var month=allsheets[i].getName().split(' ')[0];
    if(months.indexOf(month)>-1)
    {
      n6s.push([allsheets[i].getRange('N6').getValue().slice(8,-4)]);
    }
  }
  var graphsht=ss.getSheetByName('Graphs');
  graphsht.getRange(2,1,n6s.length,1).setValues(n6s);
}

Answer 2

Okay, so the problem was with the function getRange().setValues() function inside getMonthTotals.

It expected to get the Object[][] type but instead was getting a simple array of strings. I've managed to overcome this problem by looping and using the setValue(string) function instead of setValues(Object[][]).

Here's what I've come up with:

function getMonthsTotals() {  
  var sheets = ss.getSheets();
  var values = [];

  for (var i = 0; i < sheets.length; ++i) {
   var sheet = sheets[i];

    if (isMonthSheet(sheet.getName())) {
      var value = sheet.getRange('N6').getValue().slice(8, -4);
      values.push(value);
    }
  }

  for (var i = 1; i <= values.length; ++i) {
   graphSheet.getRange('A' + i).setValue(values[i - 1]);
  }
}