I developed a script that basically takes answers sent from the forms to the sheets, copies them from tab1 to tab2 inside the sheets, but one of those answers is an image and I found that if the script has more than 1 million pixels, I tried to work around it this of diversifying forms but always gives some error, sometimes the script completes but does not fulfill its function.
script1:
function onFormSubmit(e) {
// obter informações sobre a última linha inserida
var sheet = SpreadsheetApp.getActiveSheet();
var lastRow = sheet.getLastRow();
var colB = sheet.getRange(lastRow, 2).getValue();
var colC = sheet.getRange(lastRow, 3).getValue();
// redimensionar a imagem, se necessário
var blob;
try {
blob = UrlFetchApp.fetch(colC).getBlob();
var size = blob.getBytes().length;
if (size > 1000000) {
blob = resizeImage(blob);
var file = DriveApp.createFile(blob);
colC = file.getUrl();
file.setTrashed(true);
}
} catch (e) {
Logger.log("Erro ao redimensionar imagem: " + e);
}
// inserir informações na planilha de evidências
var evidencias = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("evidencias");
evidencias.getRange("B7").setValue(colB);
if (blob) {
try {
var image = evidencias.insertImage(blob, 2, 10, 8, 15);
image.setHeight(240);
image.setWidth(400);
} catch (e) {
Logger.log("Erro ao inserir imagem na planilha: " + e);
}
}
}
function resizeImage(blob) {
var MAX_WIDTH = 800;
var MAX_HEIGHT = 600;
var image = ImgApp.open(blob);
var width = image.getWidth();
var height = image.getHeight();
if (width > MAX_WIDTH || height > MAX_HEIGHT) {
if (width / MAX_WIDTH > height / MAX_HEIGHT) {
height = Math.round(height * MAX_WIDTH / width);
width = MAX_WIDTH;
} else {
width = Math.round(width * MAX_HEIGHT / height);
height = MAX_HEIGHT;
}
image = image.doResize(width, height);
}
return image.getAs("image/jpeg");
}
script2:
// Adicione o ID da pasta onde as imagens serão armazenadas
const PASTA_ID = "1FtfoTwaj2_8pQBF4WN8YY8RLi-cWz2rPrxX7poYU0LcsDmf6U532DEbLJJXxZSUdSACqxqYB";
function onFormSubmit(e) {
var sheet = SpreadsheetApp.getActiveSheet();
var lastRow = sheet.getLastRow();
var colB = sheet.getRange(lastRow, 2).getValue();
var colC = sheet.getRange(lastRow, 3).getValue();
// redimensionar a imagem, se necessário
var blob;
try {
var file = DriveApp.getFileById(colC);
var thumbnailLink = file.getThumbnailLink();
if (thumbnailLink != undefined) {
var thumbnail = UrlFetchApp.fetch(thumbnailLink).getBlob();
var image = ImgApp.open(thumbnail);
image = image.doResize(500);
blob = image.getAs("image/jpeg");
} else {
blob = file.getBlob();
}
} catch (e) {
Logger.log("Erro ao redimensionar imagem: " + e);
}
// inserir informações na planilha de evidências
var evidencias = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("evidencias");
evidencias.getRange("B7").setValue(colB);
if (blob) {
try {
var image = evidencias.insertImage(blob, 2, 10, 8, 15);
image.setHeight(240);
image.setWidth(400);
} catch (e) {
Logger.log("Erro ao inserir imagem na planilha: " + e);
}
}
Logger.log("Dados inseridos na planilha de evidências");
}
I expected that the script had taken the images from the drive and then had them reduced and attached to a range of merged cells (B11:H25) in the correct tab, however only the texts passed and the scripts failed in the part of the images.