Search code examples
google-sheetsbutton

Google Sheets - Submit button that sends cells value equal to 0

I have this script for a button that will send the values in certain cells from a given workbook and sheet to another sheet in a different workbook:

function Jan() {
const ss = SpreadsheetApp.getActive();
const fsh = ss.getSheetByName("sheet1");
const dss = SpreadsheetApp.openById(gobj.globals.ssid);
const dsh = dss.getSheetByName("sheet2");
const rgl = fsh.getRangeList(["H2","D4","I5","J5","F24","F25","F26","F27","F28"]);
let vs = rgl.getRanges().map(r => !r.isBlank()?r.getValue():rgl).filter(e => e);
if (vs.length == rgl.getRanges().length) {
dsh.getRange(dsh.getLastRow() + 1, 1, 1, 17).setValues([vs]);
} else {throw new Error ("REQUIRED FIELD IS EMPTY");
return;
}
}

As you can see, this script will give an error message when the cell is empty. However, it does the same when the value of a cell is equal to 0. Is there a way to avoid this, and make the script also submit 0 values, as 0 is a valid value to be sent?

Solution

  • Use .getDisplayValue()

    If the .getValue() function does not recognize the value of 0, you may use .getDisplayValue() instead. To apply this function to your code, replace:

    let vs = rgl.getRanges().map(r => !r.isBlank()?r.getValue():rgl).filter(e => e);

    with:

    let vs = rgl.getRanges().map(r => !r.isBlank() ? r.getDisplayValue() : rgl).filter(e => e);

    Side Note:

    I noticed that in:

    const rgl = fsh.getRangeList(["H2","D4","I5","J5","F24","F25","F26","F27","F28"]);

    there are only 9 elements in the array. You may want to change:

    dsh.getRange(dsh.getLastRow() + 1, 1, 1, 17).setValues([vs]);

    to:

    dsh.getRange(dsh.getLastRow() + 1, 1, 1, 9).setValues([vs]);

    because you will get an error with regards to different array size/range.

    Reference: