Back

Technologies:

javascriptjavascript
avatar
Tolerim
a month ago

Is it possible to create variables in Javascript or Google Apps Script that are based on the values of adjacent cells?

As someone who is new to Javascript/Google Apps Script, I appreciate any assistance you can provide. In my project, I am using a Google Sheet to organize my data as shown in the image below:

Google Sheet example

My question is whether there is a way to search for a value in Column A and create a variable with the value of the cell to its right in Column B. For instance, if "Client Name" is found at cell 6,1, can I create a variable clientNameInput from the value of cell 6,2? If I move the row down two cells and "Client Name" becomes cell 8,1, will the clientNameInput variable reflect the value of cell 8,2? Currently, I am making a separate variable for every value on each row, such as:

  const fourByFourInput = SpreadsheetApp.getActiveSheet().getRange(5,2).getValues();
  const clientNameInput = SpreadsheetApp.getActiveSheet().getRange(6,2).getValues();
  const clientDOBInput = SpreadsheetApp.getActiveSheet().getRange(7,2).getValues();
This approach works, but it means that I have to manually update each variable with the new row values if I move rows around in the sheet. Any suggestions would be appreciated. Thank you.

Answers(2)
avatar
Vogi
a month ago
Verified Answer
Yes, there is a way to search for a value in Column A and create a variable with the value of the cell to its right in Column B. You can use the getRange() method to get the range of cells you want to search in and then use the getValues() method to get the values of those cells. Here's an example code that should work for you:
function getValueFromColumnA() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var clientNameInput;
  for (var i = 0; i < data.length; i++) {
    if (data[i][0] == "Client Name") {
      clientNameInput = data[i][1];
      break;
    }
  }
  Logger.log(clientNameInput);
}
This code will search for "Client Name" in Column A and create a variable clientNameInput with the value of the cell to its right in Column B. If you move the row down two cells and "Client Name" becomes cell 8,1, the clientNameInput variable will reflect the value of cell 8,2. I hope this helps! Let me know if you have any other questions.

Sources

get value in one column in spreadsheet using google apps scriptgoogle apps script - Get column values by column name not column index ...Read & write cell values | Google Sheets | Google Developers
avatar
Tolerim
a month ago
Yes, you can achieve this with JavaScript in Google Apps Script. You can use a for loop to iterate over the values in Column A, check if the value matches your search term, and if it does, set the variable to the value of the cell to the right of it in Column B. Here's an example of how you can achieve this:
function getVariableFromColumnB() {
  const searchValue = "Client Name";
  const sheet = SpreadsheetApp.getActiveSheet();
  const lastRow = sheet.getLastRow();
  
  for (let i = 1; i <= lastRow; i++) {
    const currentValue = sheet.getRange(i, 1).getValue();
    if (currentValue === searchValue) {
      const variableName = sheet.getRange(i, 2).getValue();
      // Use the variableName variable here
      break; // Exit the loop after finding the first match
    }
  }
}
In this example, we start by setting the search term in the searchValue variable. We then get the active sheet and the last row on the sheet using the getLastRow() method. Next, we use a for loop to iterate over all the rows from 1 to the last row. Inside the loop, we get the value of the cell in Column A for the current row using the getRange() and getValue() methods. We then check if the value matches the search term using an if statement. If we find a match, we set the variableName variable to the value of the cell to the right of it in Column B using the getRange() and getValue() methods again. You can now use the variableName variable later in your code. Finally, we exit the loop using the break statement after finding the first match, assuming that there's only one match.
;