Google Spreadsheet Generation Loop

google-ss-gen.cfm

<cfset start = now()>

<!--- collect all the users into an array --->
<cfset userArray = ArrayNew(1)>

<!--- 2 item array to toggle back and forth for each user createing 2 SS for 1 user --->
<cfset templateArray = ["REDACTED","REDACTED","REDACTED"]>
<cfset ssFoldersArray = ["REDACTED","REDACTED","REDACTED"]>
<cfset questionTitles = ["Work Streams","Project Budget Estimate","Testing Phase Budget Estimate"]>
<cfset quesitonObjectIds = ["1","2","3"]>


<!--- variables we need for the loop to function and collect as needed --->
<cfset errorArray = ArrayNew(1)>
<cfset loopCounter = 1>
<cfset ssCounter = 1>


<!--- select 5 users that are not in the la_app_ss table 3 times and do what we need to to set them up --->
<cfquery dbtype="odbc" datasource="#application.dsn#" name="queryuser">
	SELECT TOP 5 *
	FROM REDACTED
	WHERE userid NOT IN (SELECT userid FROM REDACTED GROUP BY userid HAVING COUNT(userid) = 3)
</cfquery>

<!--- collect all the newly created users and their data --->
<cfloop query="#queryuser#">
	<cfset userStruct = StructNew()>
	<cfset userStruct.userid = queryuser.userid>
    <cfset userStruct.userName = queryuser.chiefExecFirstName & ' ' & queryuser.chiefExecLastName>
    <cfset userStruct.city = queryuser.city>
    <cfset userStruct.state = queryuser.state>

	<cfset userArray[currentRow] = userStruct>
</cfloop>

<!--- all good, lets fire away --->
<cfset getSS()>

<!--- google ss creation --->
<cffunction name="getSS">
	<!--- this is the id of the template ss we want to copy --->
	<cfset template = '#templateArray[ssCounter]#'>
   
    <!--- this is the city /state and name to populate in SS cell --->
    <cfset username = "#userArray[loopCounter].userName#">
    <cfset city = "#userArray[loopCounter].city# #userArray[loopCounter].state#">
    
    <!--- this is the name of the ss file we want to create --->
	<cfset name = "#ReReplace(username, ' ','-','ALL')#-#ReReplace(city, ' ','-','ALL')#-#ReReplace(questionTitles[ssCounter], ' ','-','ALL')#">
    
    <!--- this is the folder on the Drive to save the SS to --->
    <cfset folder = '#ssFoldersArray[ssCounter]#'>
	
    <!--- let's see if the user already has this question in the DB, so we dont have duplicates --->
    <cfquery dbtype="odbc" datasource="#application.dsn#" name="queryquestion">
    	SELECT * FROM REDACTED WHERE userid = '#userArray[loopCounter].userid#' AND questionObj = '#quesitonObjectIds[ssCounter]#'
    </cfquery>
    
    <cfif queryquestion.recordCount EQ 0>
        <cfhttp url="https://script.google.com/LINK-TO-CODE/exec?id=#template#&name=#name#&folder=#folder#&username=#username#&city=#city#" method="get" result="result" />
            <cfif result.StatusCode EQ "200 OK">
            <!--- success: update the DB --->
            <cftry>
                <!--- inserting --->
                <cfquery dbtype="odbc" datasource="#application.dsn#" name="updateuser" result="result">
                    INSERT INTO LA_app_spreadSheet (questionObj, userid, spreadsheetLink, questtitle) VALUES ('#quesitonObjectIds[ssCounter]#', '#userArray[loopCounter].userid#', '#result.FileContent#', '#questionTitles[ssCounter]#')
                </cfquery>
                
                <cfset nextSS()>
                
              <cfcatch type="any">
                <!--- error: record the userid of the fail, but move on --->
                <cfset ArrayAppend(errorArray,userArray[loopCounter].userid)>
                <cfmail to="REDACTED" from="REDACTED" type="html" subject="Error Inserting DB Record">
                    #userArray[loopCounter].userid#
                    <cfdump var="#cfcatch#">
                </cfmail>
                
                <cfset nextSS()>
              </cfcatch>
            </cftry>
        <cfelse>
            <!--- error: record the userid of the fail, but move on --->
            <cfset ArrayAppend(errorArray,userArray[loopCounter].userid)>
            <cfmail to="REDACTED" from="REDACTED" type="html" subject="Error Google Return">
                #userArray[loopCounter].userid#
                <cfdump var="#cfcatch#">
            </cfmail>
            
            <cfset nextSS()>
        </cfif>
    <cfelse>
    	<!--- this record already exists, let's move to the next SS or user --->
        <cfset nextSS()>
    </cfif>
</cffunction>

<cffunction name="nextSS">
	<cfset ssCounter++>
	<cfif ssCounter LTE 3>
        <cfset getSS()>
    <cfelse>
    	<cfset ssCounter = 1>
    	<cfset nextUser()>
    </cfif>
</cffunction>

<cffunction name="nextUser">
	<cfset loopCounter++>
	<cfif loopCounter LTE ArrayLen(userArray)>
        <cfset getSS()>
    <cfelse>
        <cfmail to="REDACTED" from="REDACTED" type="html" subject="Google Spreadsheet Generation Complete">
            <p>COMPLETE</p>
            <p>Started: #start#</p><p>Finished #now()#</p>
            <cfdump var="#userArray#" label="USERS">
            <cfdump var="#errorArray#" label="USERS THAT ERRORED">
		</cfmail>
        
        <!--- lets see if we need to hit the script again --->
        <cfquery dbtype="odbc" datasource="#application.dsn#" name="queryusercheck">
            SELECT TOP 5 *
            FROM REDACTED 
            WHERE userid NOT IN (SELECT userid FROM REDACTED GROUP BY userid HAVING COUNT(userid) = 3)
        </cfquery>
        
        <!--- lets see how many users are left --->
        <cfquery dbtype="odbc" datasource="#application.dsn#" name="queryusercheckAll">
            SELECT count(*) as theTotal
            FROM REDACTED 
            WHERE userid NOT IN (SELECT userid FROM REDACTED GROUP BY userid HAVING COUNT(userid) = 3)
        </cfquery>
		
        <!--- if we have a user, refresh the page --->
        <cfif queryusercheck.RecordCount GTE 1>
        	<cfoutput>
            	<html>
                	<head>
                      <meta http-equiv="refresh" content="30">
                      <title>Google Spreadsheet Generator</title>
                    </head>
                    <body>
                    	<script language="javascript"> 
							
							var count = 30; 
							var counting = setInterval(function(){ 
											count--; 
											document.getElementById('countdown').innerHTML = count; 
											if (count <= 0) { 
												clearInterval(counting); 
												document.getElementById('countdown').innerHTML = 'Running Batch...'; 
											} 
										}, 1000);
                        </script>
                    	<h1>Pausing for <span id="countdown">30</span> seconds...</h1>
                        <p>Started: #start#</p>
                        <p>Finished #now()#</p>
                        <p>#queryusercheckAll.theTotal# Remaining Users</p>
                        <cfdump var="#queryuser#" label="JUST COMPLETED">
                        <cfdump var="#queryusercheck#" label="NEXT UP">
                    </body>
                </html>
			</cfoutput>        	
        <cfelse>
        	COMPLETELY COMPLETE
        </cfif>
    </cfif>
</cffunction>