Create a gDrive folder, copy this
excel! and upload it to the gDrive (filename must have today's yyyy-mm-dd)
On gSheet, Tools -> Script Editor: paste in code.gs, change var fldr=DriveApp.getFolderById("your_folder_id_here!"); to ID of the folder you just made (line 54, code.gs), to: "[email protected]", to any email (line 329, code.gs)
Run OnOpen() 💥
Allow authorization
Go back to gSheet & click "Send e-mail" from the toolbar. Confirm & voila you will see an email like this!
Use Tools -> Script Editor -> View -> Stackdriver Logging -> Apps Script Dashboard & click on the latest run! Tip: use Logger.log() as opposed to console.log()!!
How does the excel get turned into a csv in our drive folder? 🐑
//We do a classic file-loop through our gDrive folder, called 'fldr', where we drop all our excel files >:)varfldr=DriveApp.getFolderById("your_folder_id_here!");//read more: https://developers.google.com/apps-script/reference/drive/drive-app//yummy, files!varfiles=fldr.getFiles();while(files.hasNext()){//this is what's called a file iterator!varfile=files.next(),//notice we now singular, not plural! this is just one file at a time :)fn=file.getName(),d="this_is_in_my_filename_and_it_has_to_be";if(fn.indexOf(d)>-1){//this is in case you need to check which file you want in particular//we will be using UrlFetchApp to make requests from Google servers to use one of their apis, //read more: https://developers.google.com/drive/api/v2/reference//we just need them to trust us, so we use this tokenvartoken_pls_trust_me_google=ScriptApp.getOAuthToken();//First, let's fetch our Excel's (application/vnd.ms-excel confirms this is our filetype, don't use excel 2003) byte data! (^U^)ノvarfiledata=JSON.parse(UrlFetchApp.fetch("https://www.googleapis.com/upload/drive/v2/files?uploadType=media&convert=true",{method: "POST",muteHttpExceptions: true,contentType: "application/vnd.ms-excel",payload: file.getBlob().getBytes(),headers: {"Authorization": "Bearer "+token_pls_trust_me_google}}).getContentText());//Now that we have that byte data, let's request Google to give us back a csv file (*^▽^*)vartarget_file=UrlFetchApp.fetch(filedata.exportLinks["text/csv"],{method: "GET",headers: {"Authorization": "Bearer "+token_pls_trust_me_google}})//We can make our target_file blobby and save it in our folder as a csv, with whatever name we want! fldr.createFile(target_file.getBlob()).setName(file.getName()+".csv")}break;}varcsvfile_name=file.getName()+".csv"//now we just find that file in our folder again... you can write out a separate function find_this_file, or just don't do that//totally up to you...functionfind_this_file(filename){varfiles=DriveApp.getFilesByName(filename);varresult=[];while(files.hasNext())result.push(files.next());returnresult;}varfile_list=find_this_file(csvfile_name)varcsvfile=file_list[0]
Want to parse dates on your gSheet?
//first, for comparison reasons we get today's date as yyyy-mm-dd based off your timezone!constoffset=newDate().getTimezoneOffset()constchange_d=newDate()constd=(newDate(change_d.getTime()-(offset*60*1000))).toISOString().split('T')[0]//change the date on a gSheet to mm/dd/yyyy for possible comparison with American-date-format excel data! //NOTE: for some reason with dates on a gSheet you have to use ''.concat(d) instead of just using d!!!functionformat_date(d){vars=''.concat(d)vars_d=s.split(" ");varm=['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'];for(varj=0;j<m.length;j++){if(s_d[1]==m[j]){s_d[1]=m.indexOf(m[j])+1;}}//make '1' -> '01' for January, for instance, else leave 11 for Dec as 11, not make it 011if(s_d[1]<10){s_d[1]='0'+s_d[1];}//format as mm/dd/yyyy, same as Excel datavarformatted_s=s_d[1]+'/'+s_d[2]+'/'+s_d[3];//Logger.log(formatted_s)d=formatted_sreturnd}
Want to parse your CSV data? Read it in your function as a JSON! 💥
//see how to generate the excel->csv (get var 'c' below) in the send_email.gs file :frog://Use utilities.parseCsv, and the file you use must be a blob!varc=Utilities.parseCsv(csvfile.getBlob().getDataAsString())//From here, let's say we want a JSON likeletwant_JSON={"'Header column' cell value in CSV, like cell A2 in excel": {"b2 value": "B2 value!",//like cell A2"b3value": "B3 value!"//like cell A3, the lack of a space is important later on}}//Then we can use...letwanted_JSON={},p={}//p is like header columnfor(vari=1;i<c.length;i++){vare_p={}//inside p we will nest e_pe_p[c[row-index][column-index]]=c[i][column-index]p[c[i][1]]=e_pObject.assign(wanted_JSON,p)}Logger.log('this is a JSON now! \n'+JSON.stringify(wanted_JSON,2,2))Logger.log('here are the keys! \n'+Object.keys(wanted_JSON)//Now you can read your CSV file as a JSON!
Want to compare with some gSheet data and send out the inconsistencies as an email?
inconsistent_data_points=[]//we'll send these out as a gMail later on!//To compare sheet data, we need to get it first!varsheets=SpreadsheetApp.getActiveSpreadsheet().getSheets();//Let's go through each Sheet that has a name with apple in it for no reason!for(vari=0;i<sheets.length;i++)if(sheets[i].getName().indexOf('apple')>-1){//this_tab is data range, this_tab_data is its values as array-of-arrays, and lastRow so we can loop w/ j :) letthis_tab=sheets[i].getDataRange(),this_last_row=this_tab.getLastRow(),this_tab_data=this_tab.getValues();for(varj=1;j<this_last_row;j++){leteach_sheet_B_value=this_tab_data[j][1],//this starts @ B2 is because j starts at 1! if we chose 0, we get B1each_sheet_A_value=this_tab_data[j][0];Logger.log('this should be B2 value of each sheet!'+each_sheet_B_value)//now compare to your wanted_JSON from above tip!for(vark=0;k<keys.length;k++){varthis_key=keys[k],JSON_header2=wanted_JSON[this_key]["b2 value"],JSON_header3=wanted_JSON.this_key.b3valueLogger.log('for '+this_key+' its JSON value for header #2 is '+JSON_header2)if(JSON_header2!==each_sheet_B_value){//do something if it's not equal to your Excel!! I personally want to send it out as an email, //so I will push it into my inconsistent_data_points array...Logger.log('On the excel it'ssaying ' +JSON_header2+'but on my gSheet I see '+each_sheet_B_value+'!')inconsistent_data_points.push([this_key,JSON_header2,each_sheet_B_value])}
How to send a gMail email from the data?
//Let's say you've got an array of inconsistent data points between your gSheet and your Excel called inconsistent_data_pointsvaremaildata=[["Apples",5,3],["Oranges",10,2],["Pineapples","n/a","n/a"]]//Like your Excel is saying you have 5 apples but your gSheet says 3, that you have 10 Oranges //but your gSheet says 2, and for pineapples we just don't know..varperrow=3varTABLEFORMAT='"font-family:arial, sans-serif;border-collapse:collapse;"'varTHFORMAT='style="padding-top:10px;padding-bottom:20px;padding-right:15px;padding-left:15px;text-align:left;font-weight:200;font-size:12px;border-bottom-width:5px;border-bottom-style:solid;border-bottom-color:#42A5F5; background-color: #4FC3F7"'varTRTDFORMAT='style="padding-top:5px;padding-bottom:5px;padding-right:5px;padding-left:5px;text-align:left;vertical-align:middle;font-weight:300;font-size:12px;"'//have to use inline-css for gMail API htmlBody to work properlyvarhtml='<h2 style="font-size: 12px; font-weight: 200; text-align: left; margin: 10px;">the following is on the excel but different on your gSheet!</h2>'+'<table '+TABLEFORMAT+'><th '+THFORMAT+'></th>';for(vari=0;i<emaildata.length;i++){vareach_row='<tr '+TRTDFORMAT+'><td '+TRTDFORMAT+'>';for(varj=0;j<emaildata[i].length;j++){if(emaildata[i][j]==="n/a"){emaildata[i][j]===""}else{each_row+=emaildata[i][j]}each_row+='</td><td '+TRTDFORMAT+'>';}html+='<td '+TRTDFORMAT+'>'+each_row+'</td>'//html += "<td>" + further_beyond + "</td>";varnext=i+1;if(next%perrow==0&&next!=emaildata.length){html+="</tr><tr>";}}html+="</table>"MailApp.sendEmail({to: "[email protected]",subject: "automated message about fruits",htmlBody: '<h1 style="padding-top: 50px; margin: 10px; font-size: 30px; font-weight: 300; text-align: left; margin-bottom: -15px;">Fruit Data inconsistencies!</h1>'+html})