Add cell value of a column to a specific cell based on the notes of the cell-CodePudding

I'm fairly quite new to javascript and I was playing with Apps Script to try and add the value of a cell in a specific column (C4:C14) in my spreadsheet to cell C15 when there is a Note that says 'paid'

I read through the documentation of Class Range and was able to get the values of the notes, as well as the value of the cells in cell range (C4:C14), but I am unable to set the sum based on the contents of the Insert Note on Google Sheets.

Here is the code I worked out so far as well as the screenshot of my sheet. enter image description here

function getPaid () {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  var range = sheet.getRange("C4:C14");
  var rangeval = range.getValues();

  Logger.log(rangeval);
  var comments = range.getNotes();

  for (var i = 0; i > range.length; i  ) {
    if ( range.getNotes()[i] === "paid" ) {
      sheet.getRange('C15').setValue(0   rangeval[i]);
      
    }
  Logger.log(range.getNotes[i]);
  }
  
  Logger.log(comments);

}

Can anyone help me with this? Thanks!

CodePudding user response：

Here you go also I would suggest seeing if your blocks are being executed or not, for instance, your for loop was not being called due to the wrong variable. Also always remember Google sheets always return 2d Array.

Happy Coding and welcome to the club.

function getPaid () {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheets()[0];
    
      var range = sheet.getRange("C4:C14");
      var rangeval = range.getValues();
      var comments = range.getNotes();
      var sum = 0;
      for (var i = 0; i < rangeval.length; i  ) {
       
        if ( comments[i][0] === "paid" ) {
    
          sum = sum rangeval[i][0];
        }
      }
      sheet.getRange('C15').setValue(sum)
    
    }

CodePudding user response：

Rather for educational purposes here is a 'functional' solution:

function myFunction() {
  var sh = SpreadsheetApp.getActiveSheet();

  var range = sh.getRange('c4:c14'); 
  var data  = range.getValues().flat(); // get all data in one step
  var notes = range.getNotes().flat();  // get all notes in one step

  var paid = data.filter((_,i) => notes[i] === 'paid'); // get paid values
  var sum  = paid.reduce((a,b) => a b);                 // sum of the array

  sh.getRange('c15').setValue(sum); // set the sum into the cell C15
}