I believe your goal is as follows.
- You want to convert your showing formula to Google Apps Script. When the formula is converted to Google Apps Script, you want to retrieve all values including the duplicated values from columns "J" and "K".
- Furthermore, when the values are copied, you want to also copy the notes in the cells.
- You want to put the retrieved values and notes to the cells "D2:D".
About Is there any script that can can achieve this?, I think that in this case, it is required to prepare the script for your situation. So, how about the following sample script?
Sample script:
Please copy and paste the following script to the script editor of Spreadsheet and set your sheet name, and save the script.
function myFunction() {
const sheetName = "Sheet1"; // Please set your sheet name.
// Retrieve sheet and data range.
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
const range = sheet.getRange("A1:K" + sheet.getLastRow());
// Retrieve notes.
const notes = range.getNotes();
// Retrieve values.
const [[, v], ...values] = range.getValues();
// Create an object for searching the values of column "A".
const r = new RegExp(`^${v}`);
const obj = values.reduce((o, [, , , , , , , h, , ...j], i) => {
if (h) {
j.forEach((e, l) => {
if (e.toString() && r.test(e)) {
const temp = { v: e, n: notes[i + 1][9 + l] };
o[h] = o[h] ? [...o[h], temp] : [temp];
}
});
}
return o;
}, {});
// Create new values and notes.
const newValues = values.flatMap(([a]) => obj[a] ? obj[a].map(e => [e.v]) : []);
const newNotes = values.flatMap(([a]) => obj[a] ? obj[a].map(e => [e.n]) : []);
// Put the new values and notes.
sheet.getRange("D2:D" + sheet.getLastRow()).clearContent().clearNote();
sheet.getRange(2, 4, newValues.length).setValues(newValues).setNotes(newNotes);
}
- When this script is run, the values and notes are retrieved from the sheet. And, new values and notes are created by using the value of cell "B1" and values of columns "H", "J", and "K", and those are put to column "D".
Note:
- I have no your Spreadsheet. So, I prepared a sample script by guessing your situation from your provided sample image. when this sample script was not your expected result, can you provide the sample Spreadsheet including your expected result? By this, I would like to modify the script.
References:
