0

The following Apps Script function copies certain values from a row given from one sheet to another based on a value and also formats the output.

It works perfectly well. It was written by @Tedinoz and i have updated minor things to fit my own sheet.

I need help adapting the model. Instead of pasting the model name in column 4 ("D") I need it in column 7 ("G"). What changes need to be done?

function onEdit(e){

  //check whether the value of the edited cell was 
  // i) on the Contacts Sheet AND 
  // ii) in the State column  (Column 9) AND
  // iii) had a value of PAGÓ.

  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var salessheet = "PEDIDOS2";
  var contactssheet = "FORMULARIO2";
  var sales = ss.getSheetByName(salessheet);
  var contacts = ss.getSheetByName(contactssheet);


  // Logger.log(JSON.stringify(e));

  var esheet = e.range.getSheet().getSheetName();
  var eVal = e.value;
  var ecolumnStart = e.range.columnStart;
  var erowStart = e.range.rowStart;
  var erowEnd = e.range.rowEnd;
  var ecolumnEnd = e.range.columnEnd;

  //Logger.log("DEBUG: Range details - Column Start:"+ecolumnStart+", Column End:"+ecolumnEnd+", Row start:"+erowStart+", and Row End:"+erowEnd+" and sheet = "+esheet);
  //Logger.log("DEBUG: the spreadsheet is "+e.source.getName()+", the range = "+e.range.getA1Notation()+", and the value = "+eVal);
  //Logger.log("DEBUG: the sheet name is "+esheet+", the column number = "+ecolumnStart+", and the value = "+eVal);

  if (esheet == contactssheet && ecolumnStart == 10 && eVal == "PAGÓ"){
    //Logger.log("DEBUG: this is a match");

    // ESTABLISH VARIABLES
    var headerrow=1;
    var modelcount = 0;

    var contactsrange = contacts.getRange(erowStart,5,1,6);
    // Logger.log("DEBUG: Contacts Range:"+ contactsrange.getA1Notation());
    var contactsdata = contactsrange.getValues();
    // Logger.log(contactsdata);
    var status = contactsdata[0][5];
    var name = contactsdata[0][2];
    //Logger.log("DEBUG: row = "+erowStart+" | name = "+name+" | Status = "+status);

    // create an array to update values on SALES
    var salesdata=[];

    // create a timestamp
    var newdate = new Date();
    var formattedDate = Utilities.formatDate(newdate, "America/Argentina/Buenos_Aires", "HH:mm dd/MM/yy");

    // push the time stamp and customer name onto the array
    salesdata.push(formattedDate);
    salesdata.push(name);

    // get sizes value; split to create array of the values between commas
    var sizes = contactsdata[0][0];
    var sizedata = sizes.split(',');

    // some sizes have an extra trailing comma and space, so
    // "shortcount" calculates the number of sizes when NO extra trailing comma and space
    // "longcount" calculates the number of sizes when there IS an extra trailing comma and space
    // "commaEnding" is the number of extra charaxcters when there IS an extra trailing comma and space
    var shortcount = (sizes.split(',').length - 1);
    var longcount = (sizes.split(',').length);
    var commaEnding = 3;

    // calculate the overall length of "sizes", and the index of the last Indexof
    var sizeslen = sizes.length;
    var searchTerm = ",";
    var lastindex = sizes.lastIndexOf(searchTerm)
    // Logger.log("debug: \nSizes = "+sizes+"\nTotal length = "+sizeslen+"\nIndex of the last comma = " + lastindex);

    // subtract the last index from the overall length; 
    // if it is less than/equal to the nominal commaEnding, then the number of sizes = shortcount
    // otherwise, there is a trailing comma and the number of sizes = longcount
    if(sizeslen-lastindex<=commaEnding){
      // Logger.log("DEBUG: Sizes:"+sizes+" | count:"+shortcount);
      var modelcount = shortcount;
    }
    else
    {
      // Logger.log("DEBUG: Sizes:"+sizes+" | count:"+longcount);
      var modelcount = longcount;
    }

    // loop through the number of sizes/models
    for (var m=0;m<modelcount;m++){
      var size = sizedata[m]; // get the value
      var sizelen = size.length; // get the length
      var sizetrim = size.trim(); // trim any excess spaces

      // get the size (first two characters) and convert to value
      var sizename = parseInt(sizetrim.slice(0, 2), 10);
      // get the model from character 4 to the end of the string
      var modelname = sizetrim.slice(3, sizelen);
      // Logger.log("DEBUG: m = "+m+" | size is = "+sizename+" and model = "+modelname);

      // create a copy of the salesdata in order to enable multiple lines to be copies to "SALES"
      var newArr = salesdata.slice(0);

      // push the size and the model onto the array
      newArr.push(sizename);
      newArr.push(modelname);

      // get the last row number of the "SALES sheet
      var salesLR = sales.getLastRow();

      // build the range
      var salesrange = sales.getRange(+salesLR+1,1,1,4);
      // Logger.log("DEBUG: SALES output range = "+salesrange.getA1Notation());

      // update the values on the SALES sheet
      salesrange.setValues([newArr]);

     // copy formulas down 

      sales.getRange("H2").setFormula("=COUNTIF(B:B,B2)");      
      var fillDownRange = sales.getRange (2, 8, salesLR);
      sales.getRange("H2").copyTo(fillDownRange);

      sales.getRange("I2").setFormula("=IF(COUNTIF(B:B,B2)=COUNTIFS(B:B,B2,E:E,TRUE),TRUE)");      
      var fillDownRange = sales.getRange (2, 9, salesLR);
      sales.getRange("I2").copyTo(fillDownRange);





    }

  }
  else{
    // Logger.log("DEBUG: this is NOT a match");
  }



}

Google Sheet example for testing:

https://docs.google.com/spreadsheets/d/1c9sNK6D7aemc6zXA7_yiTveOdA7tuMijfTDx2eoKFgs/edit#gid=1792749526

  • Welcome. If you didn't yet please read developers.google.com/apps-script/guides/sheets and look at the reference section for the getRange methods of Class Sheet. – Rubén Sep 13 at 22:55
  • Hi @Rubén yes i did but i cant figure out how to copy model name in column 7 instead of column 4 here: var salesrange = sales.getRange(+salesLR+1,1,1,4);, changing 4 with 7 doesnt work – sebas Sep 13 at 23:36
  • What reference for the getRange method have you read? – Rubén Sep 13 at 23:38
  • That is the right reference. Please edit the question and include a specfic question about how to use getRange(row, column, numRows, numColumns) – Rubén Sep 13 at 23:46
1

The OP's spreadsheet shows they have solved the problem described in their question.

The following is an answer to an un-asked question.

The answer is in 2 parts:

  1. an alternative to the complex formula in column D (the image formula).

  2. an alternative to the code creating formula to populate Columns D, H and I (formula creation).

Image Formula
At present this is a nested, 16 (sixteen) level, IF formula - one level for each mode/image. However, the formula isn't dynamic so that if a new product is added, or a different image is used, it is a very complex to manage.

The alternative is to create a helper sheet containing a table of products and image URLs; then use a formula based on VLOOKUP.
=IMAGE(VLOOKUP(G2,'Hoja 5'!$A$2:$B$17,2,false))


Helper Sheet
Helper sheet


The formula takes the "model name" in Column G,looks up the value in the helper table, returns the image URL, and this URL is used in the IMAGE function.

Formula Creation
Rows 122-134 of the code create formula to populate Columns D, H and I. The code copies the formula from row 2 to the last Row; that is, it copies over any existing formula. In the scheme of things, this probably does not add significantly to the execution time.

An option is to create the formula, using setFormula() only for the edited rows.


These rows can be inserted around row 14; they create variables that can be used later in the code.

  // the sheet name for building the VLOOKUP formula
  var imagerefsheetname = "Hoja 5";
  var imagerefsheet = ss.getSheetByName(imagerefsheetname);
  var ifsLR = imagerefsheet.getLastRow();
  //Logger.log("DEBUG: the last row of the image reference sheet is "+ifsLR);

This is a substitute for the existing rows 122-134.

Each element identifies the target cell, builds a formula and then executes setFormula

  // create foto formula - Column D
  var targetcell = sales.getRange(+salesLR+1,4);
  // Logger.log("DEBUG: targetcell is "+targetcell.getA1Notation());
  var formulavalue = "=IMAGE(VLOOKUP(G"+(+salesLR+1)+",'"+imagerefsheetname+"'!$A$2:$B$"+ifsLR+",2,false))";
  //Logger.log("DEBUG: formulavalue = "+formulavalue)
  targetcell.setFormula(formulavalue);

  // create count formula - Column H
  var targetcell = sales.getRange(+salesLR+1,8);
  // Logger.log("DEBUG: targetcell is "+targetcell.getA1Notation());
  var formulavalue = "=COUNTIF(B:B,B"+(+salesLR+1)+")";
  // Logger.log("DEBUG: formulavalue = "+formulavalue)
  targetcell.setFormula(formulavalue);

  // create countIF formula - Column I
  var targetcell = sales.getRange(+salesLR+1,9);
  // Logger.log("DEBUG: targetcell is "+targetcell.getA1Notation());
  var formulavalue = "=IF(COUNTIF(B:B,B"+(+salesLR+1)+")=COUNTIFS(B:B,B"+(+salesLR+1)+",E:E,TRUE),TRUE)";
  // Logger.log("DEBUG: formulavalue = "+formulavalue)
  targetcell.setFormula(formulavalue);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.