A custom script for Google Sheets that accesses data from cell "A" and populates cell "B" with information based on the value in cell "A"

Question

A custom script for Google Sheets that accesses data from cell "A" and populates cell "B" with information based on the value in cell "A"

Hey everyone,

I'm looking to create a script for Google Sheets that will check if there is a value in a cell within a row, and then populate another cell in the same row with a specific value. For instance, if a cell in row 2, column G contains anything other than empty quotes, I want to write "New issue" in the corresponding cell on row 2, column C. Here's what I have so far:

// Goal: Fill cells in Column G based on values in other cells in the same row

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

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

  }
}

The issue with my current code is that it writes "New Issue" to all cells in the range G3-G1000 if any value is found in C3-C1000. What I actually need is for it to only update the corresponding row in column G when a value exists in the respective row of column C. So, if C4 has a value, G4 should be set to "New issue", and so on for each row.

Hopefully, this clarifies things, but feel free to ask me to provide more details. I'm unsure whether using IF statements or implementing a forEach loop would be more appropriate in this situation. I'm not very familiar with how to use forEach, unfortunately. Thanks a lot for your assistance!

javascript google-apps-script google-sheets

Answer 1

Answer №1

To solve this request using Apps Script, we need to examine each element in the array and update the corresponding element in another range. To enhance efficiency, it is recommended to utilize batch operations like getValues() and setValues whenever feasible. Here is a potential solution:

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");

  // Adding the original row index in the array.
  const inputs = source.map(function (row, index) {
    row.unshift(index);
    return row;
  })
  // Filtering out rows where the 2nd index (the original content) is an empty 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();

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

  /**
   * Updating the destination range contents with the values from 'outputs',
   * which includes the previous values along with potentially new cells containing "new Issue"
   */
  dest.setValues(output);
}

I have included some logic, commented out for reference, to determine whether the value existed during the previous function call or is genuinely new.

Answer 2

To solve this request using Apps Script, we need to examine each element in the array and update the corresponding element in another range. To enhance efficiency, it is recommended to utilize batch operations like getValues() and setValues whenever feasible. Here is a potential solution:

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");

  // Adding the original row index in the array.
  const inputs = source.map(function (row, index) {
    row.unshift(index);
    return row;
  })
  // Filtering out rows where the 2nd index (the original content) is an empty 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();

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

  /**
   * Updating the destination range contents with the values from 'outputs',
   * which includes the previous values along with potentially new cells containing "new Issue"
   */
  dest.setValues(output);
}

I have included some logic, commented out for reference, to determine whether the value existed during the previous function call or is genuinely new.

Answer 3

Answer №2

It has been discovered that a straightforward equation can handle this task independently without the need for a script.

The formula "=ArrayFormula(if(len(C2:C),"New Issue",))" is sufficient to accomplish the goal.

Answer 4

It has been discovered that a straightforward equation can handle this task independently without the need for a script.

The formula "=ArrayFormula(if(len(C2:C),"New Issue",))" is sufficient to accomplish the goal.

A custom script for Google Sheets that accesses data from cell "A" and populates cell "B" with information based on the value in cell "A"

Answer №1

Answer №2

Similar questions

What is the best way to take control of DOM alterations?

Focus on selecting each label within a table using JavaScript

Ways to verify the order of the information within an array extracted from a file using JavaScript

Steps to solve React error message: "Warning: Every child in a list should have a distinct 'key' prop"

Ant Design radio group buttons are currently dysfunctional

Tips for adding a CSS marker to the Videogular timeline at a designated time

Aframe Descend Rotation

List of random items:1. Red pen2

"Is there a JavaScript code snippet that can retrieve information from a JSON file

Is there a way to create a function that is able to return both a value and a promise

Tips for consolidating multiple JavaScript files from various directories into one single file?

sending parameters to package.json scripts

Interacting with dynamically created input fields using Jquery from a JSON loop

Obtaining verified user information through Express using a JWT token

Utilize express compression in Node.js to compress JSON data from the REST API endpoint

Integrate a fresh component and include a hyperlink to the URL simultaneously

Utilizing only a fraction of my dataset, I created a barplot in D

The hamburger menu on the responsive navbar fails to open when clicked on

Select various items simultaneously by pressing Ctrl and access them via form submission

Converting user input from a string to an object in JavaScript: a comprehensive guide