I have two cells side-by-side. One is the field label (B2) and the other is the value (C2):
I have code that updates a database on another sheet, and at the end of the code, I want the Record ID (variable recordID in the code) entered into cell C2. The catch is that I use this code on multiple sheets, and the location of these two fields changes from sheet to sheet (however, these two fields are always on the ActiveSheet where the code is running). So, I want the code to search for the field label ("Record ID:") and enter the value recordID into the cell offset one column to the right.
CodePudding user response：
Using Apps Script an approximation will be:
- Get all of the values of the sheet
- Iterate over them looking for the string
Record ID - Get the adjacent cell value
Sample code
/**
* Search for a given string inside a sheet and set his value
* @constructor
* @param {string} searchTerm - String for look at
* @param {string} valueToRecordId - String containing the new value
* @param {object} sheet - And instance of the class Sheet
*/
function spreadSearch(searchTerm = "Record ID:", valueToRecordID, sheet) {
// Using the method getRange(row, column, numRows, numColumns)
// The start row and column must be 1.
// The numRows and numColumns, are obtained by getLastRow and getLastColum
let range = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues()
// Iterate over the values obtained
for (let i = 0; i < range.length; i ) {
for (let j = 0; j < range[i].length; j ) {
if (range[i][j] == searchTerm) {
// Caution, as the rows and columns start with the number 1,
// we need to add it to get the correct cell.
sheet.getRange(i 1, j 2).setValue(valueToRecordID)
return "OK"
}
}
}
// The string is not found in the sheet
return "NOT FOUND"
}
More information:
CodePudding user response：
I believe your goal is as follows.
- You want to search the value of
Record ID:from the active sheet, and want to put the value ofrecordIDto the right side cell of the searched cell.
In this case, how about the following sample script? In the following sample script, TextFinder is used.
Sample script:
This sample supposes that there are several the values of Record ID: in the active sheet.
function myFunction() {
const recordID = "###"; // Please set your value.
const sheet = SpreadsheetApp.getActiveSheet();
const rangeList = sheet.createTextFinder("Record ID:").findAll().map(r => r.offset(0, 1).getA1Notation());
if (rangeList.length == 0) return;
sheet.getRangeList(rangeList).setValue(recordID);
}
This sample supposes that there is only one value of Record ID: in the active sheet.
function myFunction() {
const recordID = "###";
const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.createTextFinder("Record ID:").findNext();
if (!range) return;
range.offset(0, 1).setValue(recordID);
}