logo
down
shadow

Google App Script - Make numeric entries 0 if conditions in another column match


Google App Script - Make numeric entries 0 if conditions in another column match

Content Index :

Google App Script - Make numeric entries 0 if conditions in another column match
Tag : google-apps-script , By : Zelos
Date : November 26 2020, 06:23 AM

around this issue If you only need to check contains "do not count", then you don't need separate conditions for each type of text.
The other change is that if there is no match, the result is null, not 0.
if(data[n][0].toString().match('do not count') != null){
  data[n][2] = 0;
}

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

How to make duplicate entries in xml with google apps-script


Tag : xml , By : user119985
Date : March 29 2020, 07:55 AM
I hope this helps you . Since you are converting the xml object to string, you can always create xml as string from the beginning.
function createXml(e) {
  var data = [["clipitem-1","video",1,1],["clipitem-2","audio",1,2]];
  var xml ='<xmeml version="4"><sequence><clipitem>';
  for(var i=0;i<data.length;i++){
    xml+="<link><linkclipref>"+data[i][0]+"</linkclipref><mediatype>"+data[i][3]+"</mediatype><mediatype>"+data[i][3]+"</mediatype></link>"
  }
  xml+="</clipitem></sequence></xml>";
  var blob = Utilities.newBlob(xml, 'text/html', e.parameter.filename + '.xml');
  return blob;
}

Google Script, find match value from one column with another column


Tag : javascript , By : DotNetWise
Date : March 29 2020, 07:55 AM
will help you I am try to make "Color check", It will change color when value input is detected. , Will be easier if you create a map of values to check for first.
function checkScriptCheck() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");

  var datePaymentRange = sheet.getRange("A:A");
  var datePaymentValues = datePaymentRange.getValues();
  var dateInputValues = sheet.getRange("B:B").getValues();

  var datePaymentColumn = datePaymentRange.getColumn();
  var checkResultValueColumn =sheet.getRange("C:C").getColumn()

  //cleaning color
  datePaymentRange.setBackground("white");

  //create map of values to check for
  var inputDates = {};
  for (var i = 1; i < dateInputValues.length; i++) { // Exclude header row
    var inputDate = dateInputValues[i][0];
    if (inputDate != "") { // Exclude blank values
      inputDates[inputDate] = true;
    }    
  }

  //check and coloring
  for (var i = 1; i < datePaymentValues.length; i++) { // Exclude header row
    var paymentDate = datePaymentValues[i][0];
    if (inputDates[paymentDate]) {
      sheet.getRange(i+1, datePaymentColumn).setBackground("#00ff00");
      sheet.getRange(i+1, checkResultValueColumn).setValue("check");
    }
  }
}

How to find index of the first match in the column using Google sheets script?


Tag : google-apps-script , By : Gerhard Miller
Date : March 29 2020, 07:55 AM
wish helps you in
var lookupRangeValues = sheet.getRange(1, 1, lastRow, 1).getValues()[0];
function getRowIndex() {
  var lookupValue = "car";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("List");
  var lastRow = sheet.getLastRow();
  var lookupRangeValues = sheet.getRange(1, 1, lastRow, 1).getValues();
  var index = -1;
  lookupRangeValues.forEach(function (row, i){
     if (row[0] == lookupValue )
     {
       index = i;
       return (false);
     }
  });
  Logger.log(index);
}

How to match one column and replace a different (corresponding) column in another sheet using google apps script?


Tag : google-apps-script , By : codelurker
Date : March 29 2020, 07:55 AM
I wish did fix the issue. This function adds the new categories to the first sheet which in my case is Sheet1. I could have just as easily replaced the first with the second category in Sheet 1 but this way it's easier to tell whether the function caught the correct data.
function findCats() {
  var ss=SpreadsheetApp.getActive();
  var sh1=ss.getSheetByName('Sheet1');
  var rg1=sh1.getRange(2,1,sh1.getLastRow()-1,sh1.getLastColumn()+1);
  var vA1=rg1.getValues();
  var sh2=ss.getSheetByName('Sheet2');
  var rg2=sh2.getRange(2,1,sh2.getLastRow()-1,sh2.getLastColumn());
  var vA2=rg2.getValues();
  var obj2={nA:[]};
  for(var i=0;i<vA2.length;i++) {
    obj2[vA2[i][0]]=vA2[i][1];
    obj2.nA.push(vA2[i][0]);
  }
  for(var i=0;i<vA1.length;i++) {
    var idx=obj2.nA.indexOf(vA1[i][0]);
    if(idx>-1) {
      vA1[i][2]=obj2[vA1[i][0]];
    }
  }
  rg1.setValues(vA1);
}
function so5664033702() {
  var ss=SpreadsheetApp.getActive();  
  var tarsheet=ss.getSheetByName('Old Categories');
  var tarLC =tarsheet.getLastColumn();
  var tarLR = tarsheet.getLastRow();
  var tarRange=tarsheet.getRange(2,1,tarLR-1,tarLC);
  //Logger.log("DEBUG: Target range ="+tarRange.getA1Notation());
  var tarValues=tarRange.getValues();
  // get the Cat2 values so that the range can be easily updated after the loop
  var tarcat2Range = tarsheet.getRange(2,6,tarLR-1);
  //Logger.log("DEBUG: Target Cat2 range ="+tarcat2Range.getA1Notation());
  var tarcat2Values = tarcat2Range.getValues();
  var srcsheet=ss.getSheetByName('New Categories');
  var srcLR = srcsheet.getLastRow();
  var srcRange=srcsheet.getRange(2,1,srcLR-1,3);
  //Logger.log("DEBUG: Source Range ="+srcRange.getA1Notation());
  var srcValues=srcRange.getValues();
  // create array to build names  
  var tarNames = [];
// loop through Old Category Names to pupulate the array  
  for (z=0;z<tarValues.length;z++){
    tarNames.push(tarValues[z][1]);
  }   
  // loop through the new category Names to find the match on Old Categories
  // the value "c" will be the row number.
  for (i=0;i<srcValues.length;i++){
    var c = tarNames.indexOf(srcValues[i][0]);
    //Logger.log("DEBUG: c = "+c+", value: "+srcValues[i][0]+", cat2:"+tarcat2Values[c][0] +", new Cat2:"+srcValues[i][2]);
    // if c = -1, then there is no match; otherwise update the OldCategory/category2 value with the value from New category
    if (c!=-1){
      tarcat2Values[c][0] = srcValues[i][2];      
    }
  } // end of the New category Loop
  // setValues for the Cat2 range with the updated values.
  tarcat2Range.setValues(tarcat2Values); 
}

Lookup Values in one column and if match add the values from a different column Google Script Google Sheets


Tag : google-apps-script , By : Singularity
Date : March 29 2020, 07:55 AM
To fix this issue I am trying to make a script that will go thru all the values of one column and add the quantities of the matching column.
function countingBuckets() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('b');
  var rg=sh.getRange(1,1,sh.getLastRow(),2);
  var vA=rg.getValues();
  var cb={bA:[]};
  vA.forEach(function(r,i){
    if(!cb.hasOwnProperty(vA[i][0])) {
      cb[vA[i][0]]=vA[i][1];
      cb.bA.push(vA[i][0]);
    }else{
      cb[vA[i][0]]+=vA[i][1];
    }  
  });
  sh.appendRow(["Results"]);
  cb.bA.forEach(function(e,i){
    sh.appendRow([e,cb[e]]);
  });
}

function countingBuckets() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('b');
  var rg=sh.getRange(1,1,sh.getLastRow(),2);
  var vA=rg.getValues();
  var cb={bA:[]};
  vA.forEach(function(r,i){
    if(!cb.hasOwnProperty(vA[i][0])) {
      cb[vA[i][0]]={cells:0,sum:0};
      cb[vA[i][0]].sum=vA[i][1];
      cb[vA[i][0]].cells=1;
      cb.bA.push(vA[i][0]);
    }else{
      cb[vA[i][0]].sum+=vA[i][1];
      cb[vA[i][0]].cells+=1;
    }  
  });
  sh.appendRow(["Results"]);
  cb.bA.forEach(function(e,i){
    sh.appendRow([e,cb[e].sum,cb[e].cells]);
  });
}
Related Posts Related QUESTIONS :
  • Cant search files with underscore in name with Google Apps Script
  • How can I create a dependent drop down menu in the html interface
  • How to count the number of characters before a certain word appearing several times in a cell in Google Sheets?
  • Finding all possible complete pairings from a list in Google Sheets
  • Hide / show specific Sheets tabs for specific users on shared file
  • How do I use the replyTo option parameter of replyAll()?
  • Improve response speed Google sheet script
  • How to link email address too Google Form answer?
  • How to find the row and column of a certain string in a Google Spreadsheet through Google App Scripts
  • Trigger for Google Sheets
  • Is there a way to make this loop more efficient?
  • Inserting variable data into an existing Google Sheet in Google Scripts
  • How to run a function when specific range is edited
  • Hiding different columns on Google sheet using script?
  • How to send html email with variable as "send to" address in Google Apps Script
  • How to use Apps Script to create multiple forms that feed into one sheet WITH SHEET TITLES
  • Share App Script Web App Between Multiple Google Sheets
  • retrieve google forms edit urls and maintain existing data structure
  • Invalid transfer on left side. (line 1, "Tests" file)
  • Error with deleting items from Google Form
  • Cloud build API deploy:run on Google Apps Script doesn't work
  • Does Column Index changes upon adding a column manually in a spreadsheet
  • Is it possible to do Input-based queries in Apps Script?
  • Extract the exact subjects and update the google sheets using apps script
  • Conversion of a Google Form ItemType via Apps Script
  • Display a clickable link on screen in Google Apps Script
  • How to encrypt/decrypt URL parameters in Google Apps Script?
  • Google Sheets filename in cell via formula instead of a script?
  • Google Sheets custom function bug "Unknown function" after successful run
  • Iterate through column, find match and send email based on cell value
  • How to modify details of only a particular occurrence of a recurring calendar event using google app script?
  • How do I make a Google Sheets custom formula that gives the current cell the color of the input cell?
  • Pass spreadsheet data in addRows() as an array
  • getActiveCell() always returns 1,1
  • &amp; is always being decoded (which I don't want) using HtmlOutput or HtmlTemplate
  • How do I loop through a single column of dates in Google Sheets and then send an email based on the date
  • Can i use Google app script for developing google chrome extension
  • How to set absolute reference in G-Sheets using Google Script
  • How to use Vlookup within an OnEdit function
  • How to convert Google Apps script blob to a base64 encoded string?
  • How to Place Two Different Types of Time Stamps in One Sheet?
  • Why can't lock value of User input?
  • How to copy edited cell data to new spreadsheet, into cell and sheet dependent on the edited cell?
  • How can I populate multiple choice questions in a Google Form from data within a Google Sheet
  • Create checkbox in cell by typing in another cell
  • How to make a form using Google Sheets script editor
  • How to get older versions of Google Spreadsheet data?
  • How to use scope "https://www.googleapis.com/auth/script.webapp.deploy"
  • Get a Cell value as value for function
  • Google Script ModalDialog/ ModelessDialog setWidth and setHeight feature not working
  • How to send a custom message when adding a viewer to Google Spreadsheet via Google Apps Scripts?
  • Looping in Creating PDF in Google Sheet
  • Is it possible to create a gmail label and choose its color using Google Apps Script?
  • Can I protect cells (user not allowed to edit) and run script that makes changes in these cells (e.g. hide row, unhides
  • Google Sheets custom sidebar: How to display a current cell value in sidebar without reload the sidebar?
  • oAuth disabled on google script
  • getOAuthToken returns null when called from Google Sheets
  • Use google-apps script url in project html
  • How to adjust as custom size margins and paper size in script to save google spreadsheet in PDF?
  • Google Apps Script HtmlServices template variables and global variables
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com