Google Apps Script for Beginners: Start Automating Google Sheets

[@NiviWord]

Thankyou Adam for putting up this beginners video on writing app script. I am a complete novice in this area and was finding it really hard to navigate through this vast field. However, because of your video study guide I wrote my first app script and it worked. I hope you will add more such beginners tutorials 😊.

[@analyticswithadam]

Have a few on the schedule also chatGPT is a great help 🙌🏿

[@vectorddev]

Great video, Adam Thanks so much! Looking forward to the upcoming tutorials, especially the genAI integrations!

[@analyticswithadam]

Thanks Troy. Already made a video on the topic if your interested Google Sheets with Gemini API: Create AI-Powered Functions Using App Script! kzbin.info/www/bejne/roCwma14iLuDgZY .. Going to go a lot more indepth in the future

[@Allen-App-Tools]

Fantastic, love this!

[@analyticswithadam]

Thanks

[@robertvarga9641]

Beautiful! Thanks a lot! I found some scripts for formatting only certain words of a cell, but for me they didn't run sadly, I was getting some errors. One that I remember was something about "var getBody" or something like this. I would really appreciate the help. The script that formats (font color, pref), certain word(s) in a sheet, or sheets.

[@analyticswithadam]

Try ChatGPT it’s amazing at debugging apps script

[@AnshulMarele-gbaa]

Hi, I have my Excel file which is updated through an odbc connection. But how can I sync/connect that Excel data into Google Sheets that could be updated automatically in periodic intervals. Any Solution? Thanks

[@infraia]

Cool Adam! Thanks

[@UncleF115]

this is very helpful. may i know is it possible to call script function from sheet cell like how we use a buildin function? by just type =func name in the cell.

[@analyticswithadam]

It is, I’m planning to make a video on it. Here is the Google explanation developers.google.com/apps-script/guides/sheets/functions

[@IanThompson-r6u]

Do you know how to write the script to make a save/enter button, if so can I pay you to write me one

[@analyticswithadam]

In the ribbon or on the page?

[@chrisder1814]

hello I ask artificial intelligence to create code for me so that I can use the open a I API in Google sheet, but the script I need does not work and when it corrects me it does not work no Plus, could you help me understand what's going on?

[@EndroPora]

how does the script reconnect the google form with the spreadsheet on the original sheet

[@analyticswithadam]

Two possible ways - this is a bound script which means it’s attached to the sheet. For an unbound script or a bound script attaching to another sheet you just capture the sheets id and connect to it that way

[@keithmerrill4058]

Adam can you use th today function in app script?

[@analyticswithadam]

Yes, you can use vanilla JavaScript

[@keithmerrill4058]

@@analyticswithadam can you please put how in the description or do a video because I am new at this

[@khabbazz89]

How many times can run. Script on free gmail account?

[@analyticswithadam]

No limit

[@khabbazz89]

@analyticswithadam i try run code to import gmailes to google sheet, i setup triggar to run every 5m after 12hours i got error message because of limit times run. By the way im from syria. Is that make limit about times to run code on Google script

[@analyticswithadam]

@@khabbazz89 Apologies, I was wrong. Two limits, 1000 api executions per day and sending 100 emails... While your 5 min trigger is only 288 execution per day unless you are calling each mail as 1. Have you anything else running that might be pumping these API calls up on your account?

[@khabbazz89]

@@analyticswithadam i've another question can i host telegram bot on app script so still answer users (/start) and aprommot bot commands?

[@analyticswithadam]

@@khabbazz89 I don't think so it mainly interacts with Google Tools. Im sure you could pull data from a Telegram API but not host anything

[@bleclerc1573]

Hi i have this code i wrote but i can't figure out how to make it copy to next available row think you can help me

[@bleclerc1573]

function copyRowsWithSetValues() {let spreadSheet = SpreadsheetApp.getActiveSpreadsheet();let sourceSheet = spreadSheet.getSheetByName('bob');let sourceRange = sourceSheet.getDataRange();let sourceValues = sourceRange.getValues();let rowCount = sourceValues.length;let columnCount = sourceValues[0].length;let targetSheet = spreadSheet.getSheetByName('noel');let targetRange = targetSheet.getRange(1, 1, rowCount, columnCount);targetRange.setValues(sourceValues)}

[@analyticswithadam]

Try ChatGPT it’s pretty good at debugging and fixing apps script

[@chrisder1814]

hello When there is no module to integrate an API, do you have to use appscript?

[@analyticswithadam]

It’s a good option to do curl commands and it can handle outputs. What are you looking to plug in?

[@chrisder1814]

@analyticswithadam I don't know anything about code, all I can do is copy and paste I watched a tutorial in which the KZbinr explained how to copy and paste the openAI API into a Google sheet module in order to use chatgpt in Google sheet and so I ask you how can I use another API when there is no module available?

[@chrisder1814]

​@@analyticswithadam​ I'm asking you this question because I don't know anything about coding and I'm starting to use make software And I'm wondering about other automation software, what other software do you use? Do you also create Chrome or WordPress extensions? Do you also use custom gpt?

[@analyticswithadam]

@@chrisder1814 Hi Chris, I use Power Automate which is a microsoft tool but people in my company use UI Path for automation. It depends on the tech stack they are all similar. I do not create extensions, just focused on data analytics for finance so use whatever helps me there

[@chrisder1814]

@@analyticswithadam hello could I send you an email

[@vivekjoshi3769]

Hi Adam! How much JavaScript knowlege is required for this? I came across this while going though a job description & I am thinking to learnt this tool. I only know python & R language.

[@analyticswithadam]

Hi Vivek , A little bit of JS to customise but its mostly internal api functions with the Google workspace apis. I’ve been getting help off Claude and chat gpt with the coding… there is a good course on Udemy too that’s fairly cheap.

[@analyticswithadam]

If you have a good handle on basic programming syntax it’s not a heavy lift. I’m building some super useful things at my company and am not an expert at JS

[@analyticswithadam]

Another great way to learn is to record a macro then look at the code it’s created

[@vivekjoshi3769]

@@analyticswithadam Recording with macro & seeing the code seems a cool idea. One more..unrelated question as you seem to be working in tech. Sorry to bother 😅 Actually learning JavaScript was on my mind but I couldn't decide it. I am not from Engineering background. I am trying to make a career change from non-tech field. I learned data analytics tools and techniques, build projects also but the market is bad right now for career transitioners. I am thinking to reconsider my goals and learn web development but that requires a lot of time. I got some career gap as I am not working. Would learning Front End to start working in tech be a good decision at this point? I know some basic html and costs stuff. I have a JS course on Udemy. My only problem is my huge career gap which may impact when applying. Pretty entangled and don't have friends in tech who could help. So, thought to ask you.

[@vivekjoshi3769]

*html and css stuff.

[@komalpatil9672]

pls increase the audio

[@RadenReynaldo]

"Hi sir! I'm having some trouble with my App Script and could really use your help. Would you be able to take a look at it for me?"

[@analyticswithadam]

What are you having problems with?

[@RadenReynaldo]

@@analyticswithadam "I'm a senior high school student and I'm just starting out with App Scripts. I'm working on a research project and could really use some help creating an App Script. Would you be able to guide me through the process?"

[@RadenReynaldo]

@@analyticswithadam Here is the layout: Columns: Column 1 ("Student ID"): Identifies each student with a unique number. Column 2 ("Student Name"): Provides the name of the student. Columns 3-18 ("Subject 1 - Subject 9"): Track attendance for each subject. Each subject has two columns - "Time In" and "Time Out" - to record when the student entered and left the class. Column 19 ("Attendance Status"): This column is likely used to determine if the student is present, late, skip, or absent. Rows: Each row represents a student. The first column (labeled "Student ID") identifies each student with a unique number which is the RFID card. The second column ("Student Name") tells us the name of the student corresponding to that row. The remaining columns (Subject 1 through Subject 9) track the student's attendance in each subject.

[@analyticswithadam]

@@RadenReynaldo Have go writing the code with ChatGPT and if you are having any specific issues I can help.