Google Sheet script that reads cell “a” and writes

Question

Good morning all,

I need to write a script for Google Sheets that checks if a cell in the row contains a value it will write a value to a column on that row. For example, If cell in row 2, column G has a value other than "" write "New issue" in cell on row 2, column C. I have this so far:

// Purpose: To populate cell in Column G if another in row cell contains 
// something other than null, or ""

function formatSpreadsheet() {
  var sheet = SpreadsheetApp.getActive();
  var data = sheet.getRange("C3:1000").getValues();

  if (data !== "") {
    sheet.getRange("G3:G1000").setValue("New Issue");

  }
}

The problem with this is that if a value is found in C3 - C1000, it writes "New Issue" to all cells in range G3 - G1000. I need it to where it only writes it to that corresponding row. So if C4 has a value, G4 should be set to "New issue" and if C5 has a value then G5 should be set to "New issue", etc.

Hopefully this makes sense, I can try to clear it up as much as possible. I don't know if "IF statements" are helpful in this scenario or if I need a for each loop. I don't know how to use for each though sadly. Thank you for your help!

Answer 1

Turns out a simple formula can do this on it's own without a script.

"=ArrayFormula(if(len(C2:C),"New Issue",))" is all it takes.

Answer 2

An Apps Script solution to this request needs to inspect each array index, and write to the corresponding index in the other range. To maximize speed, we want to use the batch operations getValues() and setValues when possible. One possible solution would look like this:

function setNewIssueLabels() {
  const sourceRangeA1 = "C3:C1000", destStart = "G3";

  const sheet = SpreadsheetApp.getActive().getActiveSheet(),
        source = sheet.getRange(sourceRangeA1).getValues();
  if (!source.length || source[0].length !== 1)
    throw new Error("Expected single-column source range with at least 1 row");

  // Include the original row index in the array.
  const inputs = source.map(function (row, index) {
    row.unshift(index);
    return row;
  })
  // Keep rows where the 2nd index (the original content) is not a null string.
  .filter(function (iRow) {
    return iRow[1] !== "";
  });

  var dest = sheet.getRange(destStart);
  if (dest.getNumRows() < source.length)
    dest = dest.offset(0, 0, source.length, 1)
  const outputs = dest.getValues();

  // For every row in 'inputs' (read: every row in the source range that
  // has a value), do stuff.
  inputs.forEach(function (iRow) {
    var originalIndex = iRow[0];
    // var originalContent = iRow[1];
    // var isActualNewIssue = outputs[originalIndex] === "";
    // if (isActualNewIssue) {
    outputs[originalIndex] = "New Issue";
    // } else {
    //   foo();
    // }
  });

  /**
   * Overwrite the contents of the destination range with the 'outputs' values,
   * which will be whatever was previously there, and possibly additional
   * cells with "new Issue"
   */
  dest.setValues(output);
}

I've added and commented out some logic for determining if the value was present on the previous invocation of the function, or if it is genuinely new.