Changing an #VALUE! in a google sheet

Question

I have a google sheet and an apps script that I am working on, but I am running into an issue within it. I have two columns on one sheet, B and C that contain various values. B will contain the word Middle, and C will be the actual data that I need.

I use a formula in column G, that read the values from columns B, and C and pull out the relevant data that matches 'Middle' It puts anything that matches Middle in column G and puts the data from column C in the adjoining H cell. The formula that I am using for column G looks like this.

=IF (ISERROR(QUERY('Announcements'!B2:C30;"select * where B contains 'Middle'")), "", QUERY('Announcements'!B2:C30;"select * where B contains 'Middle'"))

and then column H which contains this:

=CONTINUE(G2, 1, 2)

When the sheet formula runs, it pulls the correct data, but the issue that I am running into is that if there isn't any data to pull that matches the criteria (Middle), then the H2 cell gets a - in it, and the app-script throws a #VALUE! into the google doc that it is populating when it runs.

The app-script currently looks like this:

var sh10 = sh2.getRange('H2:H20').getValues(); //Middle

I have tried modifying this code that I found

var sheet = ss.getSheetByName("Announcements")
var active = sheet.getActiveCell('H2');
var cell = active.getValue();
if (cell === "#VALUE!"){
active.setValue("");
};

But when I run the script, I get the error message of "Cannot find method getActiveCell(string). (line 64, file "Test"

My end goal is to be able to have the sheet and script run, and and if there isn't any data to insert in the place holder for the doc, just put a blank space in that particular place holder and move on to the next part. These are the parts where the script counts the total number of matches to Middle and inserts the data into the place holder

var sh21 = sh2.getRange(3,12).getValue(); // Middle
var announcement2 = ""; for (var i = 0; i <= 20 - sh21; i++) { announcement2 += sh10[i]+"\n"; } // Middle

Any help would be greatly appreciated.

Thank you,

Paul

The question is not clearly explained, however as per my understanding what you wanted to implement is -- 1. Clear the contents in Col-G and Col-H. Add the formula for Column G and the to H. 2. You wanted to find if the B and C columns contains "Middle", so you can add the string to Col G and a count value Col H.3. Do you want to implement this in Apps script function? Confirm and edit the question in clear way. — KRR

Alan Wells Alan Wells · Accepted Answer · 2014-12-11T04:28:25

I notice that you have a variable named ss, but I don't see a line of code getting a reference to the spreadsheet:

var ss = SpreadsheetApp.getActiveSpreadsheet();

If you don't have that line, then the next few lines of code that depend on ss won't work.

If you put in a Logger.log() statement to check your variable, you can see what the variable is being set to:

var ss = SpreadsheetApp.getActiveSpreadsheet();
Logger.log("The value of my ss variable: " + ss);

var sheet = ss.getSheetByName("Announcements")
Logger.log("sheet variable: " + sheet);

Then VIEW the LOG after the code has run. I'm guessing the sheet is probably null, and that is causing the error.

I'm not saying that this answers your question, but to debug the problem, we have to systematically go through a process.

Changing an #VALUE! in a google sheet

2 Answers