I started using Google Apps Script yesterday and I'm struggling in running the code successfully when I attempt to get a range of cells instead of just one cell.This is the complete code, it will show a pop-up message if the condition is true:

function CheckPlanNumber() {
  // Fetch the monthly sales
  var planNumbersRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Plan Numbers").getRange("B2");
  var planNumbers = planNumbersRange.getValues();
  var ui = SpreadsheetApp.getUi(); 
  // Check totals sales
  if (planNumbers == "XXXX") {
    ui.alert('Contact - Plan Number missing!');
    }
}

However, when I try to extend the range to more cells to go through at least 10 cells and validate them, it doesn't show the pop-up message.

function CheckPlanNumber() {
  // Fetch the monthly sales
  var planNumbersRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Plan Numbers").getRange("B2:B10");
  var planNumbers = planNumbersRange.getValues();
  var ui = SpreadsheetApp.getUi(); 
  // Check totals sales
  if (planNumbers == "XXXX") {
    ui.alert('Contact - Plan Number missing!');
    }
}
javascript google-apps-script 
3 Answers

Range.getValues return values in 2 dimensional array, so if you have a table like this | | A | B | | :---: | :---: | :---: | | 1 | Name | Plan numbers | | 2 | - | 123 | | 3 | - | XXX | | 4 | - | 456 |

Range.getValues( 'B2:B4' ) will return

[
      [ "123" ]
    , [ "XXX" ]
    , [ "456" ]
]

You will have to loop through each value and compare them.

var sheet            = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Plan Numbers");
var planNumbersRange = sheet.getRange("B2:B10");
var planNumbers      = planNumbersRange.getValues();
var ui               = SpreadsheetApp.getUi(); 

for ( var row = 0; row < planNumbers.length; row++ ) {
    if ( planNumbers[ row ][ 0 ] == "XXXX" ) {
        ui.alert( 'Contact - Plan Number missing at row ' + ( row + 2 ) );
    }
}

answered Jan 12


getCell() returns a Range object, in this case the range represents a single cell, but the Range object may also represent a group of cells. You are looking for the Value of the cell, which can be retrieved from the Range by using getValue().

Logger.log(status.getValue());

See: 

https://developers.google.com/apps-script/reference/spreadsheet/range 

https://developers.google.com/apps-script/reference/spreadsheet/range#getValue()

By the way, the approach you are using (fetching one cell at a time) is very inefficient. Instead, you should fetch the entire column with getRange(), then get all the values with getValues(). GetValues() will return an Array, with all of your status values.

These functions are all documented at the links above.

answered Jan 13


Google Sheets can be accessed using SpreadsheetApp class, Google Docs can be accessed using DocumentApp class, Google Drive can be accessed using DriveApp class, Gmail can be accessed using GmailApp class

There is also a Code.gs file opened in the editor. It has a default function, which is blank, named myFunction()

function myFunction() {

}

Delete the myFunction() code block so that your editor is now blank. Then, copy and paste the following code:

function writeHelloWorld() {   var greeting = 'Hello world!';   var sheet = SpreadsheetApp.getActiveSheet();   sheet.getRange('A1').setValue(greeting); }

Copy and paste the following function into your editor, then click the Run button to execute it.

function createDocument() {
   var greeting = 'Hello world!';

   var doc = DocumentApp.create('Hello_DocumentApp');
   doc.setText(greeting);
   doc.saveAndClose();
}

You can add the code for the custom menu within the onOpen() function. A trigger will then execute your code inside this function every time you open the spreadsheet. Here’s an example:

function onOpen(e) {

   var ui = SpreadsheetApp.getUi();
   ui.createMenu('My Custom Menu')
      .addItem('First item', 'function1')
      .addSeparator()
      .addSubMenu(ui.createMenu('Sub-menu')
         .addItem('Second item', 'function2'))
      .addToUi();
}

function function1() {
   SpreadsheetApp.getUi().alert('You clicked the first menu item!');
}

function function2() {
   SpreadsheetApp.getUi().alert('You clicked the second menu item!');
}

Notice the following script. The getBitcoinPrice() function gets the data in the last 24 hours from the Bitstamp trading platform and outputs them in a sheet.
 

function getBitcoinPrice() {
   var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

   // Get the sheet with the name Sheet1
   var sheet = spreadsheet.getSheetByName("Sheet1");
   var header = ['Timestamp', 'High', 'Low', 'Volume', 'Bid', 'Ask'];

   // Insert headers at the top row.
   sheet.getRange("A1:F1").setValues([header]);

   var url = 'https://www.bitstamp.net/api/ticker/';

   var response = UrlFetchApp.fetch(url);

   // Proceed if no error occurred.
   if (response.getResponseCode() == 200) {

      var json = JSON.parse(response);
      var result = [];

      // Timestamp
      result.push(new Date(json.timestamp *= 1000));

      // High
      result.push(json.high);

      // Low
      result.push(json.low);

      // Volume
      result.push(json.volume);

      // Bid (highest buy order)
      result.push(json.bid);

      // Ask (lowest sell order)
      result.push(json.ask);

      // Append output to Bitcoin sheet.
      sheet.appendRow(result);

   } else {

      // Log the response to examine the error
      Logger.log(response);
   }
}

answered Jan 13


Login and Submit Your Answer
Browse other questions tagged  javascript  google-apps-script or ask your own question.