How do I Add Formula Countif from Other Spreadsheet When appendROW?

Happy New Normal Year.
this Topic from its Solved. Thanks to @TIMAI2 solution.
All data send to spreadsheet hasilscan

But For some reason need to verify from other Spreadsheet verifikasi based data from spreadsheet hasilscan, some data was verified but still appear as no change for Spreadsheet hasilscan. so i have a idea to modify script

function doGet(e) {
return message("Error: Please Try Again");

var data,blob,data2,blob2;

function doPost(e) {
if (!e.parameters.filename || !e.parameters.file || !e.parameters.imageformat || !e.parameters.filename2 || !e.parameters.file2 || !e.parameters.imageformat2) {
return message("Error: Please Try Again");
} else {
var imgf = e.parameters.imageformat[0].toUpperCase();
var imgf2 = e.parameters.imageformat2[0].toUpperCase();
var mime =
    (imgf == 'BMP')  ? MimeType.BMP
  : (imgf == 'GIF')  ? MimeType.GIF
  : (imgf == 'JPEG') ? MimeType.JPEG
  : (imgf == 'JPG')  ? MimeType.JPEG
  : (imgf == 'PNG')  ? MimeType.PNG
  : (imgf == 'SVG')  ? MimeType.SVG
  : false;
  var mime2 =
    (imgf2 == 'BMP')  ? MimeType.BMP
  : (imgf2 == 'GIF')  ? MimeType.GIF
  : (imgf2 == 'JPEG') ? MimeType.JPEG
  : (imgf2 == 'JPG')  ? MimeType.JPEG
  : (imgf2 == 'PNG')  ? MimeType.PNG
  : (imgf2 == 'SVG')  ? MimeType.SVG
  : false;

  if (mime && mime2) {    
  data = Utilities.base64Decode(e.parameters.file, Utilities.Charset.UTF_8);
  blob = Utilities.newBlob(data, mime, e.parameters.filename);
  data2 = Utilities.base64Decode(e.parameters.file2, Utilities.Charset.UTF_8);
  blob2 = Utilities.newBlob(data2, mime2, e.parameters.filename2);
  var imageID = DriveApp.getFolderById('1YkETbgeTEcHV2Qhd5QZg0zQXtYi9xcZZ').createFile(blob).getId();
  var imageID2 = DriveApp.getFolderById('1YkETbgeTEcHV2Qhd5QZg0zQXtYi9xcZZ').createFile(blob).getId();
 // var lat =[0];
 // var lon = e.parameters.lon[0];
 // var add = e.parameters.address[0];
  var name = e.parameters.filename[0];
  var name2 = e.parameters.filename2[0];
  var viewurl = "" + imageID;
  var viewurl2 = "" + imageID2;
var tglscanwil= e.parameters.tglscanwil[0];   
var nosamb= e.parameters.nosamb[0];
var nama= e.parameters.nama[0];
var alamat= e.parameters.alamat[0];
var kec= e.parameters.kec[0];
var gol= e.parameters.gol[0];
var masli= e.parameters.masli[0];
var mawal= e.parameters.mawal[0];
var makhir= e.parameters.makhir[0];
var m3= e.parameters.m3[0];
var tglscan= e.parameters.tglscan[0];
var thnbln= e.parameters.thnbln[0];
var petugas= e.parameters.petugas[0];
var kelainanid= e.parameters.kelainanid[0];
var diedit= e.parameters.diedit[0];
var makhir1blnlalu= e.parameters.makhir1blnlalu[0];
var makhir2blnlalu= e.parameters.makhir2blnlalu[0];
var makhir3blnlalu= e.parameters.makhir3blnlalu[0];
var tglhp= e.parameters.tglhp[0];
var latlong = e.parameters.latlong[0];
var metodbaca= e.parameters.metodbaca[0];        
var petugasstr= petugas.toString();        
var ts = Utilities.formatDate(new Date(), "GMT+8", "dd/MM/yyyy HH:mm:ss");
var ss = SpreadsheetApp.getActive();
var sh = ss.getSheets()[0];
return message("Success: Image Uploaded and Data Saved");
} else {
return message("Error: Please Try Again");

function message(msg) {
return ContentService.createTextOutput(msg);

So i have idea, every time sh.appendRow created. put formula =countif(spreadsheetverifikasi!A:A,nosamb) to column

var diedit= e.parameters.diedit[0];

how to apply that in app script?

I hope when Spreadsheet verifikasi changed, Spreadsheet Hasilscan automatically get change too

im Newbie learn google apps script.

thaks for your help again

You will need to bring in the data from the "other spreadsheet" to a separate sheet on your main spreadsheet using the importrange function (you do this on the spreadsheet, not in google apps script). Then you can reference that data.

Not too sure I understand what your formula

is trying to achieve...?

ok im done to =importrange sir.

btw my formula purpose to give value in cell when Spreadverifikasi change, it count nosamb at spreadsheetVerifikasi range A;A by nosamb at spreadsheetHasilcan. result must be 0 if same nosamb counted, if result 1 or more it mean there count same nosamb data at both spreadsheet. i use this result formula to "sign" that data has verified in other sheet.

the main point is how var diedit= e.parameters.diedit[0];changes the value when the other sheet is changed automatically

That formula will give the same result in every row...

thats why, i need to add formula to var edit . so var edit can change if other sheet change.

Assuming you place the formula in column AA, the formula should look like this:


this checks the cell in column B which is nosamb

You need to include the formula in the appendRow line in single quotes, I don't think you can use a variable, but could be wrong.

sh.appendRow([tglscanwil, nosamb,makhir,m3,kelainanid,nama,alamat,kec,gol,masli,mawal,tglscan,thnbln,petugasstr,

Tested it work. after 1 week search try and error, you found solution less than a hour.
now i know how to add formula with appendRow so simple :sweat:

btw thanks @TIMAI2 for your solution :pray: :pray: :pray: :pray:
HAPPY NEW NORMAL YEAR 2021 :partying_face: :partying_face:

That is why we are here - sometimes we get it right :smiley:


This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.