// To learn how to use this script, refer to the documentation:// https://developers.google.com/apps-script/samples/automations/equipment-requests/*Copyright 2022 Google LLCLicensed under the Apache License, Version 2.0 (the "License");you may not use this file except in compliance with the License.You may obtain a copy of the License at    https://www.apache.org/licenses/LICENSE-2.0Unless required by applicable law or agreed to in writing, softwaredistributed under the License is distributed on an "AS IS" BASIS,WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.See the License for the specific language governing permissions andlimitations under the License.*/// Update this variable with the email address you want to send equipment requests to.constREQUEST_NOTIFICATION_EMAIL="request_intake@example.com";// Update the following variables with your own equipment options.constAVAILABLE_LAPTOPS=['15" high Performance Laptop (OS X)','15" high Performance Laptop (Windows)','15" high performance Laptop (Linux)','13" lightweight laptop (Windows)',];constAVAILABLE_DESKTOPS=["Standard workstation (Windows)","Standard workstation (Linux)","High performance workstation (Windows)","High performance workstation (Linux)","Mac Pro (OS X)",];constAVAILABLE_MONITORS=['Single 27"','Single 32"','Dual 24"'];// Form field titles, used for creating the form and as keys when handling// responses./** * Adds a custom menu to the spreadsheet. */functiononOpen(){SpreadsheetApp.getUi().createMenu("Equipment requests").addItem("Set up","setup_").addItem("Clean up","cleanup_").addToUi();}/** * Creates the form and triggers for the workflow. */functionsetup_(){constss=SpreadsheetApp.getActiveSpreadsheet();if(ss.getFormUrl()){constmsg="Form already exists. Unlink the form and try again.";SpreadsheetApp.getUi().alert(msg);return;}constform=FormApp.create("Equipment Requests").setCollectEmail(true).setDestination(FormApp.DestinationType.SPREADSHEET,ss.getId()).setLimitOneResponsePerUser(false);form.addTextItem().setTitle("Employee name").setRequired(true);form.addTextItem().setTitle("Desk location").setRequired(true);form.addDateItem().setTitle("Due date").setRequired(true);form.addListItem().setTitle("Laptop").setChoiceValues(AVAILABLE_LAPTOPS);form.addListItem().setTitle("Desktop").setChoiceValues(AVAILABLE_DESKTOPS);form.addListItem().setTitle("Monitor").setChoiceValues(AVAILABLE_MONITORS);// Hide the raw form responses.for(constsheetofss.getSheets()){if(sheet.getFormUrl()===ss.getFormUrl()){sheet.hideSheet();}}// Start workflow on each form submitScriptApp.newTrigger("onFormSubmit_").forForm(form).onFormSubmit().create();// Archive completed items every 5m.ScriptApp.newTrigger("processCompletedItems_").timeBased().everyMinutes(5).create();}/** * Cleans up the project (stop triggers, form submission, etc.) */functioncleanup_(){constformUrl=SpreadsheetApp.getActiveSpreadsheet().getFormUrl();if(!formUrl){return;}for(consttriggerofScriptApp.getProjectTriggers()){ScriptApp.deleteTrigger(trigger);}FormApp.openByUrl(formUrl).deleteAllResponses().setAcceptingResponses(false);}/** * Handles new form submissions to trigger the workflow. * * @param {Object} event - Form submit event */functiononFormSubmit_(event){constresponse=mapResponse_(event.response);sendNewEquipmentRequestEmail_(response);constequipmentDetails=Utilities.formatString("%s\n%s\n%s",response.Laptop,response.Desktop,response.Monitor,);constrow=["New","",response["Due date"],response["Employee name"],response["Desk location"],equipmentDetails,response.email,];constss=SpreadsheetApp.getActiveSpreadsheet();constsheet=ss.getSheetByName("Pending requests");sheet.appendRow(row);}/** * Sweeps completed and cancelled requests, notifying the requestors and archiving them * to the completed sheet. * * @param {Object} event */functionprocessCompletedItems_(){constss=SpreadsheetApp.getActiveSpreadsheet();constpending=ss.getSheetByName("Pending requests");constcompleted=ss.getSheetByName("Completed requests");constrows=pending.getDataRange().getValues();for(leti=rows.length;i>=2;i--){constrow=rows[i-1];conststatus=row[0];if(status==="Completed"||status==="Cancelled"){pending.deleteRow(i);completed.appendRow(row);console.log(`Deleted row:${i}`);sendEquipmentRequestCompletedEmail_({"Employee name":row[3],"Desk location":row[4],email:row[6],});}}}/** * Sends an email notification that a new equipment request has been submitted. * * @param {Object} request - Request details */functionsendNewEquipmentRequestEmail_(request){consttemplate=HtmlService.createTemplateFromFile("new-equipment-request.html",);template.request=request;template.sheetUrl=SpreadsheetApp.getActiveSpreadsheet().getUrl();constmsg=template.evaluate();MailApp.sendEmail({to:REQUEST_NOTIFICATION_EMAIL,subject:"New equipment request",htmlBody:msg.getContent(),});}/** * Sends an email notifying the requestor that the request is complete. * * @param {Object} request - Request details */functionsendEquipmentRequestCompletedEmail_(request){consttemplate=HtmlService.createTemplateFromFile("request-complete.html");template.request=request;constmsg=template.evaluate();MailApp.sendEmail({to:request.email,subject:"Equipment request completed",htmlBody:msg.getContent(),});}/** * Converts a form response to an object keyed by the item titles. Allows easier * access to response values. * * @param {FormResponse} response * @return {Object} Form values keyed by question title */functionmapResponse_(response){constinitialValue={email:response.getRespondentEmail(),timestamp:response.getTimestamp(),};returnresponse.getItemResponses().reduce((obj,itemResponse)=>{constkey=itemResponse.getItem().getTitle();obj[key]=itemResponse.getResponse();returnobj;},initialValue);}

<!DOCTYPE html><!-- Copyright 2022 Google LLC Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at      http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.--><html>  <body>    <p>    A new equipment request has been made by <?= request.email ?>.    </p>    <p>    Employee name: <?= request['Employee name'] ?><br/>    Desk location name: <?= request['Desk location'] ?><br/>    Due date: <?= request['Due date'] ?><br/>    Laptop model: <?= request['Laptop'] ?><br/>    Desktop model: <?= request['Desktop'] ?><br/>    Monitor(s): <?= request['Monitor'] ?><br/>    </p>    See <a href="<?= sheetUrl ?>">the spreadsheet</a> to take or assign this item.  </body></html>

<!DOCTYPE html><!-- Copyright 2022 Google LLC Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at      http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.--><html>  <body>    <p>    An equipment request has been completed.    </p>    <p>    Employee name: <?= request['Employee name'] ?><br/>    Desk location name: <?= request['Desk location'] ?><br/>    </p>  </body></html>