JavaScript Objects - Iterate Through Object Keys and Values - Google Sheets Apps Script - Part 13

[@BigDave004]

I have checked my code a bunch of times and I am still not getting the information from the array to populate into the template. Can you help me figure out where I've gone wrong? Your videos are excellent and I appreciate you sharing so much of your knowledge. Thank you! Here is my code: function sendEmails() { SpreadsheetApp.getActiveSpreadsheet().getSheetByName("emails").activate(); var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var lr = ss.getLastRow(); var templateText = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Template").getRange(1, 1).getValue(); var quotaLeft = MailApp.getRemainingDailyQuota(); if((lr-1) > quotaLeft) { Browser.msgBox("You have " + quotaLeft + " messages left in your quota and you are trying to send" + (lr-1) + " emails. Emails were not sent."); } else { for (var i = 2;i

[@ExcelGoogleSheets]

With new V8 engine this line doesn't work for(var [key,val] in obj){ try this instead for (let [key, value] of Object.entries(obj)) {

[@BigDave004]

@@ExcelGoogleSheets Thank you so much! You need a patreon! If you have one, shoot me that link please.

[@ExcelGoogleSheets]

@@BigDave004 www.patreon.com/chicagocomputerclasses

[@BigDave004]

@@ExcelGoogleSheets Proudly done! Thank you again.

[@ExcelGoogleSheets]

@@BigDave004 Thanks for your support, I appreciate it!

[@ShizzleMyChizzle]

What I love about your videos is you speak so concisely that I can play your videos at 2x speed and understand what you are explaining. Great job thanks for all the epic videos!

[@davidelliott1093]

I've done GAS mailmerges for years, and I learned something today.

[@derekherzog1569]

Great videos. Really appreciate the time you put into these man you're an internet hero

[@goldylock]

From @9:27 onwards, use " for (let [key,val] of Object.entries(info)){ ..... }" instead. The old method doesnt work anymore in 2021

[@Lordgiancarlo404]

Thanks!

[@ExcelGoogleSheets]

Thank You!

[@WilsondotZeroFaustino]

Great video. Any chance of future tutorial about sending e-mails in HTML format?

[@muralasiva]

man learned a lot from ur videos...thank u very much

[@vishnutiwari7902]

Sir Your video is really awesome . please make more about java script like how to lookup multiple sheets ,query function how to apply filter from any column and filter in same place thanks

[@omnarayan7066]

Hello Sir this is very useful for me Thank you

[@teediel]

Thank you for these videos. I've been watching this month in this playlist. I'm from a VBA background and these are helpful. One question, the idea of assigning data into an object rather than purely processing/replacing it through direct string keywords, in this situation what benefit does it do over the direct replacement if you'll have to code new keys in the object everytime you'll have new data to add anyway? Or is this just for demonstration of objects? I just thought the effort is the same for the direct string replacement without using objects. Thank you.

[@billeast5263]

Thanks for the help. Look forward to more!

[@mr.nobodyx]

How to save an array-of-objects in sheets while making sure the properties stay in the same column from one row to another? PS: love your videos, they are AMAZING, keep it up!

[@tomdevisser649]

For people watching this, you need to update the code. This doesn't work anymore: for (var [key, val] in obj) Instead use this: for (let [key, val] of Object.entries(obj)) Or this, where you get the value by using obj[key]: for (let key in obj)

[@vineetverma7992]

in version 8 used let with object.entries(obj) // obj is info

[@testaccount6075]

Thanks, this actually worked! I also had to change var info to var obj.

[@oroverdino]

for (var key in infoEmail) { mailBody = mailBody.replace('{'+ key +'}', infoEmail[key]); }

[@peteevansyup]

Love your videos. Great job!

[@weasleyrichard8809]

Any suggestion to get the value for {name} twice or more times? It works fine while using it once.

[@atily900]

Thanks, how can i get replacements as bold text?

[@ripy002]

I did not understand the last part where you were talking about the “from field “ you entered Argument, passed as an object “name” in the MailApp.sendmail(). Since your template already had Chicago computer Classes at the bottom of the mail, how will this last part of passing the name: “Chicago Computer Classes” benefit or reflect.

[@ExcelGoogleSheets]

That's senders name. When you receive an email it says who sent it in the main subject line listing without having to open the email.

[@TheKeule33]

this here is gold. thy sooo much!

[@nimaanshbusinessautomation3748]

Hello Teacher, I have checked and re write my code multiple times but still getting error. Whats my actual error is when I Log only key its gave me correct data but when I loop through and Log val is shows something else. here is my code function learn(){ var info = { name: "Bharat",age: 37,gender: "male"}; for (var key in info){ Logger.log(key); } } // This works perfect and result is [21-01-15 10:00:45:121 IST] name [21-01-15 10:00:45:124 IST] age [21-01-15 10:00:45:127 IST] gender now the other one function learn(){ var info = { name: "Bharat",age: 37,gender: "male"}; for (var [key,val] in info){ Logger.log(key); Logger.log(val); } } // this is also a same code with val and result is [21-01-15 10:04:43:998 IST] n [21-01-15 10:04:44:002 IST] a [21-01-15 10:04:44:004 IST] a [21-01-15 10:04:44:007 IST] g [21-01-15 10:04:44:009 IST] g [21-01-15 10:04:44:011 IST] e I have re write this for multiple times but still not getting data what it should be. Please help me here. Thank you very much for this channel.

[@gaaraxpo]

what a brilliant way

[@vaishnaviganugapati7314]

What if you have a reoccurring field such as {Company Name} which repeats itself 4-5 times in your template. How can I replace all the values where it states {Company Name} to the one mentioned in the spreadsheet for one company name? I believe I will have to run a FOR loop and replace the value again because this loop only works once. Any suggestions as to how do I tackle that issue?

[@ExcelGoogleSheets]

In new V8 version you should be able to use replaceAll instead of replace. Otherwise you need to use regular expression, something like replace(/\{Company\sName\}/g,"My Word")

[@walterpaiva719]

Thanks, you're helping me a lot ;]

[@danielromhany]

Hey! When you replace the key in the message body, why do you using " + key + ", and not "key" ? Anyway you are making great videos, and I usually understand everything, but i could understood this. Could you (or someone) help me with this?

[@ExcelGoogleSheets]

In our template we type Dear {name}, we need to replace {name} with that persons name. If we just replace the key the result will be Dear {Joe}, but we don't want that, we need to get rid of the brackets, that's why we replace "{" + key + "}"

[@danielromhany]

Thank you!

[@RahulSharma-uj7cr]

Awesome but can you help me with attachments. I want to attach maximum 3 attachments in mail but sometimes it could 2 or 1 but there would always be attachments. how to handle those with this. waiting for your prompt revert.

[@rayanabhardwaj]

I want to send one email for the multiple entries. Probably using arrays and loops for that. i am unable to find a relevant tutorial for it. can anybody help with this?

[@rajchaitanyasingh2176]

its not working i am putting the same thing that is for (var [key , val] in info { looher.log (key) and looger log (val)} the result is not showing

[@ExcelGoogleSheets]

With new V8 engine this line doesn't work for(var [key,val] in obj){ try this instead for (let [key, value] of Object.entries(obj)) {

[@caiomonteiro4995]

I'm trying this [key, val] formula in 2021/06 and it doesn't work. It only shows me the first letter of each.

[@caiomonteiro4995]

I've already found the answer from the admin: With new V8 engine this line doesn't work for(var [key,val] in obj){ try this instead for (let [key, value] of Object.entries(obj)) {

[@bulbulahmed3098]

❤❤❤

[@santosahernandezmendoza7147]

All very well, only the end seems that it did not work, please check what happened, maybe a simple detail. Thanks for sharing, your reward is intellectual strength

[@ExcelGoogleSheets]

I'm not following you. Did you try and it didn't work? Share the code.

[@santosahernandezmendoza7147]

gracias

[@santosahernandezmendoza7147]

Everything worked perfect and I am grateful to you, except at the end of the lesson when you add the field 'name' as a new property again? or new argument?

[@KotuKediSerafettinn]

its working...

[@santosahernandezmendoza7147]

With the last fourth argument "name"?

[@serviciossdm9981]

I want to Google Sheets to send emails to only a modified row for the whole range.

[@raymondizarie9229]

If I use var messageBody = templateText.replace(/{civilite}/g,currentCivilite) .replace(/{nom}/g, currentName.bold()) .replace(/{titre}/g,currentFunction) .replace(/{date}/g,reunionDate); all the remplacements are OK {civilite} and {nom} are use twice. But if I use : var messageBody = templateText; for (var [key, val] in info ){ messageBody = messageBody.replace("/{"+ key + "}/", val ); }; it seems to be the same thing, but it does not work. The {key} are not replaced in the template. I don’t know why ! Regards RI

[@ExcelGoogleSheets]

In regular expression you use /regexstring/ to indicate that's your regular expression. In regular replace / means literally /. so instead of messageBody = messageBody.replace("/{"+ key + "}/", val ); you should do messageBody = messageBody.replace("{"+ key + "}", val );

[@raymondizarie9229]

Thank you, but what i want is to replace all occurrence of the key word, ( why / /g), the only solution I found : function _templateEngine_(info, templateText) { var messageBody = templateText; for (var [key, val] in info ){ while (messageBody.search(key) >= 0) { messageBody = messageBody.replace("{" + key + "}" , val ); }; // while }; // for return messageBody; } Thank a lot RI

[@ExcelGoogleSheets]

Yes, it's best to use REGEX for this. It's clean.

[@davidduran2984]

Hello, thank you for posting all these videos, I've been watching the playlist without problems so far. But this time I am having problems creating an object inside of an object. I am trying to run this code: function PlantillaDeAccion(){ var Informacion = { nombre: "Elisa", gender: "F", age:31, Colores: ["Amarillo","Azul","Rojo"] Direccion: {Calle: "Los Libertadores", Numero: "32", Ciudad: "Buenos Aires"} }; Logger.log(["Direccion"]["Calle"]); } But I get this error message: SyntaxError: Unexpected identifier (line 81, file "Code.gs"). Line 81 being the one that begins with Direccion. Is there something I am missing?

[@ExcelGoogleSheets]

You need a comma between each property Colores: ["Amarillo","Azul","Rojo"],

[@davidduran2984]

@@ExcelGoogleSheets Wow... thanks... that was fast. I was so focused on line 81 that somehow I forgot to check line 80. I also noticed I missed a semicolon after the logger line. I already added it (edited that part of the post), but somehow the logger shows me this: [20-04-06 16:53:51:118 CLT] null

[@ExcelGoogleSheets]

You forgot the object variable Logger.log(Informacion["Direccion"]["Calle"]);

[@ExcelGoogleSheets]

Also with new updates I suggest using console.log() instead of Logger.log()

[@davidduran2984]

@@ExcelGoogleSheets ​ Learn Google Spreadsheets Thank you very much... sorry to bother with such small details. I took notice of the console.log() too. May I make a friendly suggestion? A couple of comments below you gave an answer to Dave Jimenez which would be great if pinned. That part about the change made in V8 was frustrating until I found out what it was.

[@ConsulthinkProgrammer]

How to create this nested Object below from list in Google Sheets Sir? var subjectObject = { "Front-end": { "HTML" : ["Links", "Images", "Tables", "Lists"], "CSS" : ["Borders", "Margins", "Backgrounds", "Float"], "JavaScript": ["Variables", "Operators", "Functions", "Conditions"] }, "Back-end": { "PHP": ["Variables", "Strings", "Arrays"], "SQL": ["SELECT", "UPDATE", "DELETE"] } }

[@AbuAbdullahSaqibjeddah]

Hey the replace function is not working.

[@riteshpandey5082]

Sir This code is not working ------***********-------------------- function learn(){ var info = { name: "Lisa", gender: "female", eyecolor: "blue", age: 32 }; for(var [key,val] in info){ Logger.log(key); Logger.log(val); } } output showing like that [20-07-28 00:09:48:779 IST] n [20-07-28 00:09:48:781 IST] a [20-07-28 00:09:48:783 IST] g [20-07-28 00:09:48:784 IST] e [20-07-28 00:09:48:787 IST] e [20-07-28 00:09:48:788 IST] y [20-07-28 00:09:48:790 IST] a [20-07-28 00:09:48:792 IST] g

[@ExcelGoogleSheets]

With new V8 engine this line doesn't work for(var [key,val] in obj){ try this instead for (let [key, value] of Object.entries(obj)) {

[@riteshpandey5082]

for (let [key, value] of Object.entries(info)) you comment this code for One user, i got from there, thank You

[@rainardopuster]

You may use var data for the logging keys and list[data] for logging the values, as under: var list = { name: "John", surname: "Smith", age: 40 }; for(var data in list){ Logger.log(data); // name surname age Logger.log(list[data]); // John Smith 40

[@rastislavmadac9408]

@@rainardopuster thank you, now it make sense for me.

[@oroverdino]

for (var key in infoEmail) { mailBody = mailBody.replace('{'+ key +'}', infoEmail[key]); }

[@phoenixempire8886]

🙏🏻

[@michaelshaw7652]

Object is too big a name for Keys-Values structure. How about call it dictionary?

[@mrsmoove808]

function learn ey.. you are defying the borders of creativity ya know (takes pretentious bite out of an apple)

[@giteshkariya579]

Hi, Great way of teaching. The code explained in video did as per expectation, but I tried variation to further understand the concept, but it is not replacing first key but correctly replacing second key. Even I tried replacing both and 2nd key is only getting replaced. Pls help: function Application() { var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet7"); var lr = ss.getLastRow(); var msgDraft = ss.getRange("h1").getValue(); for(var i=1; i

[@ExcelGoogleSheets]

You need to keep overwriting the same variable var msgFinal = msgDraft.replace("{"+key+"}",value); keeps making a new msgFinal from original msgDraft so before the loop do var msgFinal = msgDraft and then in the loop use msgFinal = msgFinal.replace("{"+key+"}",value);