- You want to run the script by clicking the image on Spreadsheet.
- You want to put the images for the column "A". In this case, you want to put a lot of images.
- You want to achieve this using the simple script.
Issue and workaround:
- Unfortunately, in the current stage, when the script is run by clicking the image on the sheet, the script cannot know where the image is clicked. By this, unfortunately, in your case, when you want to run the script by clicking the image, it is required to assign each image with the different function name.
- And also, when the script is run by clicking the image, it seems that the function is searched before the global variables are run. By this, automatically installing functions using the script cannot be achieved.
From above situation, in order to achieve your goal, I would like to propose the following 2 patterns.
Pattern 1:
In this pattern, the images assigned the functions are used. In this case, as the sample, it supposes that 5 images are put to the sheet of Ark1, and each images has the function names like copyPasteValue2, copyPasteValue3 and so on.
Sample script:
The sample script is as follows. Please copy and paste the following script to the script editor.
function main(row) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName('Ark1');
var [v, d] = s.getRange(`B${row}:C${row}`).getValues()[0];
var s2 = ss.getSheetByName('Ark2');
var timeZone = ss.getSpreadsheetTimeZone();
var t = Utilities.formatDate(new Date(), timeZone, 'HH:mm:ss dd-MM-yyyy');
s2.getRange(`A${row}:C${row}`).setValues([[t,v,d]]);
}
const copyPasteValue2 = () => main(2);
const copyPasteValue3 = () => main(3);
const copyPasteValue4 = () => main(4);
const copyPasteValue5 = () => main(5);
const copyPasteValue6 = () => main(6);
- In this script, when the image which has the function name of
copyPasteValue2 is clicked, 2 which is the row of the image is given to the function of main(). By this, copyPasteValue2 can be run as the row 2.
Note:
- About
const copyPasteValue2 = () => main(2); const copyPasteValue3 = () => main(3); ,,,, the format is constant. So for example, I think that you can also create these script as a text file, then you can copy and paste them to the script editor.
If you want to insert the images to the column "A" using a script, you can use the following function. When you use this, please set the file ID of the image on Google Drive. And please set sheetName and max.
function insertImages() {
var id = "###"; // Please set the file ID of the image.
var sheetName = "Sheet1"; // Please set the sheet name.
var max = 5; // Please set the number of images you want to put.
var blob = DriveApp.getFileById(id).getBlob();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(sheetName);
sheet.setRowHeights(2, max, 64).setColumnWidth(1, 64);
for (var i = 0; i < max; i++) {
sheet.insertImage(blob, 1, i + 2).setHeight(64).setWidth(64).assignScript("copyPasteValue" + (i + 2));
}
}
Pattern 2:
In this pattern, I would like to propose to use the checkboxes instead of the image. In this case, using the OnEdit event trigger, when the checkbox is clicked, it can know the coordinate of the checkbox which was clicked. By this, the script can become simpler than that of pattern 1.
Sample script:
function main(row) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName('Ark1');
var [v, d] = s.getRange(`B${row}:C${row}`).getValues()[0];
var s2 = ss.getSheetByName('Ark2');
var timeZone = ss.getSpreadsheetTimeZone();
var t = Utilities.formatDate(new Date(), timeZone, 'HH:mm:ss dd-MM-yyyy');
s2.getRange(`A${row}:C${row}`).setValues([[t,v,d]]);
}
function onEdit(e) {
const range = e.range;
if (range.getSheet().getSheetName() == "Ark1" && e.value == "TRUE") {
main(range.rowStart);
range.uncheck();
}
}
- At above script, it supposes that the column "A" on the sheet of
Ark1 has the checkboxes.
- In this case, you can run the script using the simple trigger.
Note:
- In this case, please enable V8 at the script editor.
References:
If I misunderstood your question and this was not the direction you want, I apologize.
