เมื่อมีผู้กรอกGoogle Form อยากให้นำข้อมูลที่ผู้ใช้กรอก มาเข้าสูตรคำนวณและบันทึกเป็นคอลัมน์ใหม่ แต่ถ้าใส่สูตรไว้ตั้งแต่แรก พอมีผู้มากรอกฟอร์มสูตรก็ดันหายไปด้วย วันนี้มาดูวิธีแก้ไขกันนะครับ 🙂 🙂 🙂
ปล.บทความนี้รองรับการเพิ่ม Dropdown แบบอัตโนมัติด้วยนะครับ 🙂 🙂 🙂
// Presented by BrilliantPy // Editable let sheetName = "การตอบแบบฟอร์ม 1"; let sheetDataName = "ชีต2"; let lengthDropdown = "!$A$1:$A$5"; let indexCol = {"ประทับเวลา":0,"ชื่อ - สกุล":1,"อีเมล":2,"ชื่อ":3,"ผู้ควบคุม":4,"update_status":5} let colStatusName= "update_status"; let colFormula1 = "D"; let colDropdown = "E"; let colStatus = "F"; // Init let updatedStatus = "UPDATED"; let ss,sheet,lastRow,lastCol,range,values; async function createDropdown() { await initSpreadSheet(); for (let i=0;i<lastRow;i++) { if (i == 0) { continue; } let cur_data = values[i]; let cur_status = cur_data[indexCol[colStatusName]]; let numRow = i+1; console.log("cur_data:",cur_data) if (cur_status != updatedStatus) { addCustomData(numRow); updateColStatus(numRow); } } }; async function initSpreadSheet() { return new Promise(function(resolve) { ss = SpreadsheetApp.getActive(); sheet = ss.getSheetByName(sheetName); lastRow = sheet.getLastRow(); lastCol = sheet.getLastColumn(); range = sheet.getDataRange(); values = range.getValues(); resolve(); console.log('initSpreadSheet completed'); }); } function updateColStatus(numRow) { sheet.getRange(colStatus.concat(numRow)).setValue(updatedStatus); console.log('updatedStatus completed'); } function addCustomData(numRow) { // Add dropdown ss.getRange(`'${sheetName}'!${colDropdown}${numRow}`).setDataValidation(SpreadsheetApp.newDataValidation().setAllowInvalid(true) .requireValueInRange(ss.getRange(`'${sheetDataName}'${lengthDropdown}`), true).build()); // Add formula sheet.getRange(colFormula1.concat(numRow)).setFormula('=LEFT(B2,FIND(" ",B2)-1)'); }
I am often to blogging and i really appreciate your content. The article has really peaks my interest. I am going to bookmark your site and keep checking for new information.
Thank you. You can follow us either way.
youtube : https://www.youtube.com/@brilliantpy/videos
Facebook: https://web.facebook.com/BrilliantPy
Hi there! Do you use Twitter? I’d like to follow you if that would be okay. I’m undoubtedly enjoying your blog and look forward to new updates.
Thank you. You can follow us either way.
youtube : https://www.youtube.com/@brilliantpy/videos
Facebook: https://web.facebook.com/BrilliantPy
I see something truly special in this web site.
Thank you. You can follow us either way.
youtube : https://www.youtube.com/@brilliantpy/videos
Facebook: https://web.facebook.com/BrilliantPy
I would like to thnkx for the efforts you have put in writing this blog. I am hoping the same high-grade blog post from you in the upcoming as well. In fact your creative writing abilities has inspired me to get my own blog now. Really the blogging is spreading its wings quickly. Your write up is a good example of it.
Thank you. You can follow us either way.
youtube : https://www.youtube.com/@brilliantpy/videos
Facebook: https://web.facebook.com/BrilliantPy
Heya i am for the first time here. I found this board and I find It truly useful & it helped me out much. I hope to give something back and help others like you aided me.
Thank you. You can follow us either way.
youtube : https://www.youtube.com/@brilliantpy/videos
Facebook: https://web.facebook.com/BrilliantPy
Wow, awesome blog layout! How long have you been blogging for? you made blogging look easy. The overall look of your site is fantastic, as well as the content!
Thank you. You can follow us either way.
youtube : https://www.youtube.com/@brilliantpy/videos
Facebook: https://web.facebook.com/BrilliantPy
Some really nice stuff on this internet site, I enjoy it.
Thank you. You can follow us either way.
youtube : https://www.youtube.com/@brilliantpy/videos
Facebook: https://web.facebook.com/BrilliantPy
I love what you guys tend to be up too. Such clever work and reporting! Keep up the amazing works guys I’ve you guys to our blogroll.
Thank you. You can follow us either way.
youtube : https://www.youtube.com/@brilliantpy/videos
Facebook: https://web.facebook.com/BrilliantPy
I have been browsing online greater than 3 hours these days, yet I by no means found any interesting article like yours. It’s lovely value enough for me. In my view, if all webmasters and bloggers made just right content as you did, the net shall be much more helpful than ever before.
Thank you. You can follow us either way.
youtube : https://www.youtube.com/@brilliantpy/videos
Facebook: https://web.facebook.com/BrilliantPy
I am continuously invstigating online for ideas that can assist me. Thx!
Thank you. You can follow us either way.
youtube : https://www.youtube.com/@brilliantpy/videos
Facebook: https://web.facebook.com/BrilliantPy
Hello my friend! I want to say that this article is amazing, great written and come with almost all important infos. I?¦d like to look extra posts like this .
Thank you. You can follow us either way.
youtube : https://www.youtube.com/@brilliantpy/videos
Facebook: https://web.facebook.com/BrilliantPy
Dead written written content, Really enjoyed studying.
Thank you. You can follow us either way.
youtube : https://www.youtube.com/@brilliantpy/videos
Facebook: https://web.facebook.com/BrilliantPy