I actually can't believe that I'm asking for something as simple as this, but anyway.

I have a report hub, with entries to complete, and I'd like to get everyone's number of entries left and send a Slack message. My issue here is that the values I get are simply a list of [0.0], [0.0], ect...

Here's my code :

function Values() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("Summary!B7:B28");
var data = range.getValues();


Logger.log(data)}

And the Output: [[0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0]]

the original data is something like that:

Note that the values of entries are actually a formula (=SUM()).

I thought I could simply retrieve the data from col B, and use an index, and then do a loop that would look like this :

function Values() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var range = sheet.getRange("Summary!B7:B28");
    var data = range.getValues();
    data.forEach(SendMessage);
    }

function SendMessage(val, index){
    var rtext = "";
    var rname = "";
    
    if(val > 0) {
     switch(index) {
      case 0: 
        rtext = "";
        rname = "";
        break;
      case 1:
        rtext = "";
        rname = "";
        break;
        default: return false;}}

// here the rest of my script to send slack messages//
}

What I found strange is the fact that even if I try to a simple =IMPORTRANGE within the sheet, for the same range, it also returns 0s. SO my main issue may be the source file.

I really hope someone can help me there, because I'm lost :)

CodePudding user response：

Try this:

function Values() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("Summary!B7:B28");
  var data = range.getDisplayValues();
  Logger.log(JSON.stringify(data))
}