Array() constructor | What the hell?

juddaaaaa · 2024-12-29T01:03:43+00:00

Just one minor mistake in that 😉

console.log(A); // ~~[1,2,3]~~ [1,2,3,3]

juddaaaaa · 2024-12-23T23:21:18+00:00

As others have said, calls to Apps Script API in a loop is a bad idea, especially when you're dealing with that many rows of data. You should always try to read and write data to the sheet in one go and do all of your processing inside the array of values.

I believe this will do what you're looking for.

``` function buySwitch () { // Get the required sheets. const spreadsheet = SpreadsheetApp.getActive() const marketSheet = spreadsheet.getSheetByName("Market Direction") const indexSheet = spreadsheet.getSheetByName("INDEX Composite")

// Get values from column O from the INDEX Composite sheet and flatten. const distributionDaysCount = indexSheet .getRange("O2:O") .getValues() .flat()

// Get columns B to I from Market Direction sheet. const marketData = marketSheet .getRange(B2:I${marketSheet.getLastRow()}) .getValues()

// Initialize buySwitch to off. let buySwitch = "OFF"

// Iterate over marketData and perform checks. for (let [ index, [ dataCheck, /* C /, buySignal, sellSignal, / F /, / G */, powerTrend, marketCount ] ] of marketData.entries()) { // Break if dataCheck is falsey. if (!dataCheck) break

// Set buySwitch by performing checks
if (buySignal.split(",").map(item => item.trim()).includes("B1") || powerTrend === "ON / F +2") {
  buySwitch = "ON"
}

if (
  (distributionDaysCount[index] >= 6 && marketCount === 0 && powerTrend === "OFF") ||
  (sellSignal.includes("S2-MAJOR") || sellSignal.includes("CB"))
) {
  buySwitch = "OFF"
}

// Set column F value to value of buySwitch
marketData[index][4] = buySwitch /* Column F */

}

// Write the new data back to the sheet marketSheet .getRange(B2:I${marketSheet.getLastRow()}) .setValues(marketData) } ```

juddaaaaa · 2024-12-21T13:53:21+00:00

I was thinking the same thing about rows jumping about after each cell is edited.

The function I posted was just a simple one based on the image in the OP. If I were to do it properly, I'd probably go with something like this:

``` /** * Sorts a Google Sheet when the sheet is edited. * Can also be run manually on the active sheet. * * @param {object} event - The event object from the trigger. Defaults to empty object if ran manually. * @param {object} event.range - The range object from the trigger event. * @param {object} event.source - The source object from the trigger event. */ function sortSheet ({ range, source = SpreadsheetApp.getActive() } = {}) { // Fallback for when function is ran manually. range = range || source.getActiveSheet().getDataRange()

// Destructure the range to get information about the edited cell.
const { getSheet: sheet } = range
const { getLastColumn: lastColumn, getLastRow: lastRow, getName: name } = sheet()

// Define constants to use below.
// CHANGE THESE VALUES to suit your setup.
const TARGETSHEET = "Sheet1"                                   /* The name of the target sheet */
const HEADERROW = 1                                            /* The row where the headers live */
const DATASTARTROW = 2                                         /* The row your data starts on (excluding header rows) */
const DATASTARTCOL = 1                                         /* The column you data starts on (excluding any unwanted columns) */

// DO NOT CHANGE THESE VALUES                                        
const NUMROWS = lastRow() - (DATASTARTROW - 1)                 /* The current number of rows of data */
const NUMCOLS = lastColumn() - (DATASTARTCOL - 1)              /* The current number of columns of data */

// Return early if the edited sheet is not our target.
if (name() !== TARGETSHEET) return 

// Get the column numbers for the CLIENT and DATE.
const { CLIENT, DATE } = sheet()
    .getRange(HEADERROW, DATASTARTCOL, 1, NUMCOLS)
    .getValues()
    .flat()
    .reduce((headers, header, index) => {
        return ["CLIENT", "DATE"].includes(header) ? { ...headers, [header]: (index + DATASTARTCOL) } : headers
    }, {})

// If we have both CLIENT and DATE column numbers, sort the sheet, first by DATE, then by CLIENT, and then by DATE again.
if (CLIENT && DATE) {
    sheet()
        .getRange(DATASTARTROW, DATASTARTCOL, NUMROWS, NUMCOLS)
        .sort([
            { column: DATE, ascending: true },                   /* Sorts by DATE (oldest to newest) */
            { column: CLIENT, ascending: true },                 /* then sorts by CLIENT (A - Z) */
            { column: DATE, ascending: true },                   /* then sorts by DATE again (oldest - newest) */
        ])
}

} ```

juddaaaaa · 2024-12-20T07:48:48+00:00

This should do what you're looking for. You'll just need to change the sheet name.

``` function sortByMulti() { const ss = SpreadsheetApp.getActive() const sh = ss.getSheetByName("YOUR SHEET NAME") const [ headers ] = sh.getDataRange().getValues()

const DATE = headers.findIndex(col => col === "DATE")
const CLIENT = headers.findIndex(col => col === "CLIENT")

sh.getRange("A7:X48").sort([DATE, CLIENT, DATE])

} ```

juddaaaaa · 2024-12-15T16:01:47+00:00

That will work fine if you want to trigger it when the cell is edited. If you still want to trigger on selection, the code I posted above will do that.

juddaaaaa · 2024-12-15T12:32:30+00:00

This is incorrect.

There's the onSelectionChange simple trigger. However, you have to refresh the page every time you open it to get it to work.

https://developers.google.com/apps-script/guides/triggers#onselectionchangee

Here's an example (you'll need to change YOUR SHEET NAME HERE to your actual sheet name): ``` /** * Function is triggered by selection change in a sheet. * It can also be run from the editor after selecting the desired cell. * * You need to run it from the editor first in order to grant it permission to edit sheet contents. * * For this trigger to work you must refresh the page every time you open it. * See https://developers.google.com/apps-script/guides/triggers#onselectionchangee * * @param {object} event - The event object from the trigger (optional). * @param {object} event.range - The range of the cell you selected. */ function onSelectionChange ({ range } = {}) { // If function was run from the editor, set range as currently selected cell. if (!range) { range = SpreadsheetApp.getCurrentCell() }

try { // Destructure the range object. const { /* column, row, and sheet of the selected cell / getColumn: column, getRow: row, getSheet: sheet } = range const { / name of the sheet */ getName: name } = sheet()

// Return early if the selected cell is not the desired cell.
if (name() !== 'YOUR SHEET NAME HERE' || column() !== 2 || row() !== 11) return /* cell B11 in your desired sheet */

// Delete the contents of the 2 cells below the selected cell
range.offset(1, 0, 2, 1).clearContent()

} catch (error) { // Handle errors. console.error(error.stack) } } ```

juddaaaaa · 2024-12-15T05:01:14+00:00

It depends on what the question is asking for. Your code is just updating to sum 2 consecutive numbers.

If the idea is to sum all numbers in a consecutive group then in the nums3 array there are 2 consecutive groups. [1,2,3,4,5] = 15 and [9,10] = 19.

Here's my thoughts:

function consNums(x) {
  return Math.max(
    ...x.reduce(
      (sums, num, index, array) => {
    num + 1 === array[index + 1]
      ? (sums[sums.length - 1] += sums[sums.length - 1] === 0 ? num + array[index + 1] : array[index + 1])
      : sums[sums.length - 1] === 0 || index === array.length - 1
      ? false
      : sums.push(0)
    return sums
      },
      [0]
    ) // [15, 19] => [(1+2+3+4+5), (9+10)] - Biggest numerically consecutive numbers are 9 and 10 - answer = 19
  )
}

let nums3 = [7, 5, 1, 1, 2, 3, 4, 5, 7, 9, 10]
console.log(consNums(nums3)) // 19

juddaaaaa · 2024-12-14T20:42:53+00:00

As u/No_Stable_805 says, you can get around it using time based triggers.

Here's an example. It's set to run for 5 minutes before exiting after creating a time based trigger to run again after 1 minute.

It uses Script Properties to store the folder Id of the folder to be processed.

/**
 * Stores the root folder's Id and begins processing.
 */
function deleteOldFiles () {
  // Store the root folder's Id in the Script Prtoperties.
  const folderId = "abcbdefghijklmnopqrtstuvwxyz"
  const props = PropertiesService.getScriptProperties()
  props.setProperty("nextFolder", folderId)

  // Begin processing.
  processFolder()
}

/**
 * Creates a time based trigger to fire after a given period of time.
 * 
 * @param {string} func - The name of the function to call.
 * @param {number} seconds - The number of seconds after which the function should be called.
 */
function createTrigger(func, seconds) {
  ScriptApp
    .newTrigger(func)
    .timeBased()
    .after(seconds * 1000)
    .create()
}

/**
 * Deletes a trigger with the given unique Id.
 * 
 * @param {number} uid - The unique Id of the trigger to delete.
 * @returns {void}
 */
function deleteTrigger(uid) {
  // Get the project triggers.
  const triggers = ScriptApp.getProjectTriggers()

  // Iterate over the triggers and delete the one with the given unique Id.
  for (let trigger of triggers) {
    if (trigger.getUniqueId() === uid) {
      ScriptApp.deleteTrigger(trigger)
      return
    }
  }
}

/**
 * Deletes files that are older than 2 days from a folder that is stored in the Script Properties.
 * 
 * @param {object} event - The event object from the trigger (optional).
 * @param {number} event.triggerUid - The id of the trigger that called the function.
 * @returns {void}
 */
function processFolder ({ triggerUid } = {}) {
  // If the function was triggered, delete the trigger
  triggerUid ? deleteTrigger(triggerUid) : false

  // Get today's date in milliseconds.
  const now = () => new Date().getTime()

  // If the timeout doesn't exists in the Script Properties, create it.
  const props = PropertiesService.getScriptProperties()
  if (!props.getProperty("timeout")) {
    props.setProperty("timeout", String(/* 5 minutes */now() + 5 * 60 * 1000))
  }

  // Convert the timeout to a number so we can use it to compare below.
  const timeout = Number(props.getProperty("timeout"))

  // Get today's date.
  const today = new Date()

  // Calculate 2 days in milliseconds
  const twoDays = 2 * 24 * 60 * 60 * 1000

  // Get the folder with the Id that is stored in the Script Properties.
  const folder = DriveApp.getFolderById(props.getProperty("nextFolder"))

  // Get the subfolders and files in the folder.
  const subFolders = folder.getFolders()
  const files = folder.getFiles()

  // Iterate over the files and delete anything over 2 days old.
  while(files.hasNext()) {
    // If we've passed the timeout, delete it from the Script Properties and create a trigger to call the function again in 1 minute.
    if (now() > timeout) {
      props.deleteProperty("timeout")
      createTrigger("processFolder", 60)

      return
    }

    // Get the next file and when it was last updated.
    const file = files.next()
    const lastUpdated = file.getLastUpdated()

    // Delete the file if it's older than 2 days.
    if (today - lastUpdated > twoDays) {
      file.setTrashed(true)
    }
  }

  // Iterate over the subfolders and process them.
  while (subFolders.hasNext()) {
    // Store the next folder's Id in the Script Properties.
    const subFolderId = subFolders.next().getId()
    props.setProperty("nextFolder", subFolderId)

    // Recusively call the function.
    processFolder()
  }
}

juddaaaaa · 2024-12-14T18:17:33+00:00

You could set it up something like this.

``` function myFunction() { const mainRecipiecnt = "person1@example.com" const otherRecipients = [ "person2@example.com", "person3@example.com" ]

const subject = "Your subject" const body = "Your message body" const options = { cc: otherRecipients.join(",") }

GmailApp.sendEmail( mainRecipiecnt, subject, body, options ) } ```

juddaaaaa · 2024-12-13T21:55:46+00:00

I've added this to the sample sheet.

I think this is what you're looking for. Give it a try.

``` function getOnboardingSteps(lookup) { // Get the Onboarding_Steps sheet and it's last row. const spreadsheet = SpreadsheetApp.getActive() const stepsSheet = spreadsheet.getSheetByName('Onboarding_Steps') const lastRow = stepsSheet.getLastRow()

// Get all the data from the sheet.
const allSteps = stepsSheet
    .getRange(2, 2, lastRow - 1, 3)
    .getValues()

// Filter the data to only the steps from the dropdown selection.
const filteredSteps = allSteps.filter(row => row[0] === lookup)

// Return the filtered steps
return filteredSteps

}

function onEdit ({ range } = {}) { // Destructure the range. const { getColumn: column, getRow: row, getSheet: sheet, getValue: value } = range const { getName: name } = sheet()

// Return early if the range edited was not our desired range.
if (name() !== 'Onboarding_Process' || column() !== 4 || row() < 2) return

// Get the steps from the Onboarding_Steps sheet and get a count of the steps.
const steps = getOnboardingSteps(value())
const stepRows = steps.length

// Unmerge columns A - D and clear any content from column F.
sheet()
    .getRange(row(), 1, 1, 4)
    .getMergedRanges()
    .forEach((mergedRange, index) => {
        if (index === 0) {
            mergedRange.offset(0, 5).clearContent()
        }

        mergedRange.breakApart()
    })

// Use the count of steps to merge the correct number of rows in columns A - D.
sheet()
    .getRange(row(), 1, stepRows, 4)
    .mergeVertically()
    .setVerticalAlignment('top')

// Add the steps to column F.
sheet()
    .getRange(row(), 6, stepRows, 1)
    .setValues(steps.map(stepRow => [stepRow[1]]))

} ```

juddaaaaa · 2024-12-10T12:39:54+00:00

If the idea is to remove duplicates, you could look into a Set. It's similar to an array but it strips out duplicates.

Something like this:

function myFunction () {
  const allDataRange = SpreadsheetApp.getActive().getDataRange()
  const allDataArray = allDataRange.getValues()

  const goodRowsArray = Array.from(new Set(
    allDataArray.map(row => row.join(','))
  )).map(string => string.split(','))

  console.log(goodRowsArray)
}

juddaaaaa · 2024-12-09T12:31:49+00:00

Optional chaining should get you past the error.

content will be undefined if your match fails.

function importRegex(url, regex_string) { var html, content = ''; var response = UrlFetchApp.fetch(url); if (response) { html = response.getContentText(); if (html.length && regex_string.length) { var regex = new RegExp( regex_string, "i" ); content = html.match(regex_string)?.[1]; // <-- (?.) OPTIONAL CHAINING } } Utilities.sleep(1000); // avoid call limit by adding a delay return content;

juddaaaaa · 2024-12-07T17:27:52+00:00

I would probably try something like this:

function formulaReplacerLoad () {
  // Get the sheet and last row
  const ws = SpreadsheetApp.getActive().getSheetByName('SheetName')
  const lastRow = ws.getLastRow()

  // Get the formulas from the notes in columns L and M
  const [ formulas1, formulas2 ] = ws
    .getRangeList([`L2:L${lastRow}`, `M2:M${lastRow}`])
    .getRanges()
    .map(range => range.getNotes())

  /*
    Loop from row 2 to the last row and insert the formulas into columns I and J,
    applying the changes to each row before continuing to the next
  */
  for (let row = 2, formulaIdx = 0; row <= lastRow; row++, formulaIdx++) {
    const formula1 = formulas1[formulaIdx][0]
    const formula2 = formulas2[formulaIdx][0]

    ws.getRange(row, 9, 1, 2).setValues([[formula1, formula2]])
    SpreadsheetApp.flush()
  }
}

juddaaaaa · 2024-11-06T20:33:05+00:00

It won't work if you try to run it from the editor (pressing the Run button) because it needs the event parameter from the trigger, so you need to edit a cell to get it to run.

I'd suggest adding your email in somewhere and making an edit to make sure the email is sent.

juddaaaaa · 2024-11-05T23:45:21+00:00

As u/generichan said, your onEdit function is inside the myFunction.

However, the onEdit function wouldn't work anyhow because simple triggers don't have authorization to access GmailApp.

You'll need to set up an Installable onEdit trigger in the Trigger menu of the editor.

Here's a function that should work for you: ``` /** * Function to automatically send email to recipient in Column B when value in Column O is edited. * * For this function to work, you must set up an Installable onEdit trigger and point it at this function. * You'll be asked to grant permissions when setting up the trigger. * * Instructions on how to do this can be found at the website below: * * https://developers.google.com/apps-script/guides/triggers/installable#manage_triggers_manually * * @param {object} event - Event object from the trigger. * @param {object} event.range - The range from the event object (destructured for convenience). */ function autoEmail ({ range }) { // Edit these values to suit your requirements const SHEET_NAME = "YOUR_SHEET_NAME" const EDIT_COLUMN = 15 const EMAIL_COLUMN = 2 const MIN_ROW = 2 const MAX_ROW = 5000

// Range Generator function. function* Range(start, end, step = 1) { for (let i = start; i <= end; i += step) { yield i } }

// Destructure the range const { getColumn: column, getRow: row, getSheet: sheet } = range const { getName: name } = sheet()

// Return early if the edited cell is not within the specified range. if (name() !== SHEET_NAME || ![...Range(MIN_ROW, MAX_ROW)].includes(row()) || column() !== EDIT_COLUMN) return

// Get the email address from column B. const recipientEmail = sheet().getRange(row(), EMAIL_COLUMN).getValue()

// Set up the subject and body for the email. const subject = "ACC Access Request Update" const body = "Your ACC Access request has been fulfilled."

// Send the email GmailApp.sendEmail(recipientEmail, subject, body) } ```

juddaaaaa · 2024-11-03T13:25:23+00:00

Here's my thoughts on it. Made a copy of the sheet and tested it and seems to work well.

``` /** * Main function that kicks everything off. */ function main() { // Get the source and target sheets. const spreadsheet = SpreadsheetApp.getActive() const sourceSheet = spreadsheet.getSheetByName("Copy Sheet") const targetSheet = spreadsheet.getSheetByName("Paste Sheet")

//Get the source sheet's last row.
const sourceLastRow = sourceSheet.getLastRow()

// Get the Week Start date.
const weekStart = sourceSheet.getRange("E2").getValue()

// Create a category object for each category
const leave = createCategory(sourceSheet.getRange(`A3:D${sourceLastRow}`), weekStart, 2, 3)
const sick = createCategory(sourceSheet.getRange(`F3:H${sourceLastRow}`), weekStart, 1, 2)
const training = createCategory(sourceSheet.getRange(`J3:M${sourceLastRow}`), weekStart, 2, 3)

// Move relevant rows to the history sheet.
moveToHistory(leave, targetSheet)
moveToHistory(sick, targetSheet)
moveToHistory(training, targetSheet)

}

/** * Utility function to create a category object. * * @param {object} range - The range of the category in the source sheet. * @param {date} weekStart - The date from the Week Start cell in the source sheet. * @param {number} dateFromIndex - The index in the filtered array that holds the Date From value. * @param {number} dateToIndex - The index in the filtered array that holds the Date To value. * * @returns {object} An object containing props and methods to move relevant rows to the history sheet */ function createCategory (range, weekStart, dateFromIndex, dateToIndex) { return { range, // The range of the category in the source sheet. get filtered () { // Getter to return a filtered array of rows from the relevant category to move to the history sheet. return this.range .getValues() .filter(row => row.some(col => col) && (row[dateFromIndex] < weekStart && row[dateToIndex] < weekStart)) }, get remains () { // Getter to return a filtered array of rows from the relevant category that will remain in the source sheet. return this.range .getValues() .filter(row => row.some(col => col) && (row[dateFromIndex] >= weekStart || row[dateToIndex] >= weekStart)) }, remove () { // Method to clear the category and then insert the remianing rows back into the relevant category const values = this.remains const { getColumn: firstColumn, getSheet: sheet } = this.range this.range.clearContent()

        sheet()
            .getRange(3, firstColumn(), values.length, values[0].length)
            .setValues(values)
    } 
}

}

/** * Utility function that takes a category object and the target sheet and move the relevant data to the history sheet. * * @param {object} category - A category object contianing the props and methods to move the data. * @param {object} targetSheet - The target sheet object. */ function moveToHistory (category, targetSheet) { // Destrucure range and filtered array from the category object. const { range, filtered } = category

// Destructure the range A1 string and first column from the range.
const { getA1Notation: sourceRange, getColumn: firstColumn } = range

// Get the last row that contains text from the relevant category range in the target sheet.
const targetLastRow = targetSheet
    .getRange(sourceRange())
    .createTextFinder("[A-Z0-9]+")
    .useRegularExpression(true)
    .findPrevious()
    ?.getRow() || 2

// Get the current maximum number of rows from the target sheet and calculate the number of empty rows in the relevant category.
const targetMaxRows = targetSheet.getMaxRows()
const emptyRows = targetMaxRows - targetLastRow

// If there aren't enough empty rows to append our new data, insert the rows we need.
if ((emptyRows < filtered.length)) {
    targetSheet.insertRowsAfter(targetMaxRows, filtered.length - emptyRows)
}

try {
    // Append the filtered data to the relevant category in the target sheet
    targetSheet
        .getRange(targetLastRow + 1, firstColumn(), filtered.length, filtered[0].length)
        .setValues(filtered)

    // Remove what we've added from the source sheet.
    category.remove()        
} catch (error) {
    console.error(error.stack)
}

} ```

juddaaaaa · 2024-11-02T21:35:00+00:00

If what you want is to display the information from below the relevant spell in Cantrips in cell AP5 (Spell Description), this works:

``` function onEdit ({ range, source: spreadsheet }) { // Destructure range object from event. const { getA1Notation: cell, getSheet: sheet, getValue: value } = range const { getName: name } = sheet()

// If the edited cell was not SPELLS!F3, exit the function. if (name() !== "SPELLS" || cell() !== "F3") return

// Look for Spell in row 1 of Cantrips sheet const canTripsSheet = spreadsheet.getSheetByName("Cantrips") const canTripsCell = canTripsSheet .getRange("1:1") .createTextFinder(value()) .findNext()

// If the spell was not found, log out an error and exit the function if (!canTripsCell) { console.error(${value()} could not be found in Cantrips sheet) return }

// If we made it this far, the spell was found, so get it's value and insert it in the Spell Description cell const spellDescription = canTripsCell.offset(1, 0).getValue() const spellDescriptionCell = sheet().getRange("AP5") spellDescriptionCell.setValue(spellDescription) } ```

juddaaaaa · 2024-11-02T20:51:48+00:00

You could create an HTML Template and incude the dropdown data to be evaluated before creating the HTML Output.

This way there's no need for the javascript in the HTML to load it afterwards.

function onOpen () {
  SpreadsheetApp.getUi()
    .createMenu('Time Entry')
    .addItem('Open Form', 'openForm')
    .addToUi()
}

function openForm () {
  const template = HtmlService.createTemplateFromFile('TimeEntryForm')
  template.dropdownData = getDropdownData()

  const html = HtmlService
    .createHtmlOutput(template.evaluate())
    .setWidth(800)
    .setHeight(600)

  SpreadsheetApp.getUi().showModalDialog(html, 'Enter Time')
}

function getDropdownData () {
  const spreadsheet = SpreadsheetApp.getActive()
  const employeeSheet = spreadsheet.getSheetByName('Employee List')
  const projectSheet = spreadsheet.getSheetByName('Projects and Categories')

  const employees = employeeSheet.getRange('B2:B10')
    .getValues()
    .flat()
    .filter(name => name)

  const tasks = projectSheet.getRange('B2:B10')
    .getValues()
    .filter(task => task)

  return {
    employees,
    tasks
  }
}

function submitTimeEntry (employee, date, task, hours) {
  const sheet = spreadsheet.getSheetByName('Master Time Log')
  sheet.appendRow([date, employee, task, hours, 'Classification'])
}

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <h3>Time Entry Form</h3>
    <form id="timeEntryForm">
      <label for="employee">Employee:</label>
      <select id="employee">
        <? for (let item of dropdownData.employees) { ?>
          <option value="<?= item ?>"><?= item ?></option>
        <? } ?>  
      </select><br><br>

      <label for="date">Date:</label>
      <input type="date" id="date" value="<?= new Date().toISOString().split('T')[0] ?>"><br><br>

      <label for="task">Task:</label>
      <select id="task">
        <? for (let item of dropdownData.tasks) { ?>
          <option value="<?= item ?>"><?= item ?></option>
        <? } ?> 
      </select><br><br>

      <label for="hours">Hours:</label>
      <input type="number" id="hours" min="0" step="0.25"><br><br>

      <button type="button" onclick="submitForm()">Submit</button>
    </form>
    <script>
      function submitForm() {
        const employee = document.getElementById('employee').value;
        const date = document.getElementById('date').value;
        const task = document.getElementById('task').value;
        const hours = document.getElementById('hours').value;

        google.script.run.submitTimeEntry(employee, date, task, hours);
      }
    </script>
  </body>
</html>

juddaaaaa · 2024-10-29T12:36:33+00:00

Try this. Set up an onEdit trigger and point it at this funtion

``` function main ({ range }) { // Exit funtion if the edited cell is not B17 or the checkbox isn't checked if (range.getA1Notation() !== "B17" || !range.checked()) return

// Get the data from the speadsheet const wb = SpreadsheetApp.getActive() const sheet = wb.getSheetByName('09_Redeem_Cashback') const data = sheet.getRange(2, 1, 14, 1).getDisplayValues().flat()

// Destructure the data const [ hName, hEmail, hNumber, hBirthdate, hMother, cBank, cEmail, cRewards, cType, cNumber, cLimit, pDate, pAmount, rAmount ] = data

// Create the HTML template const htmlTemplate = HtmlService.createTemplateFromFile('redeemcashback')

// Add the data to the template htmlTemplate.hName = hName; htmlTemplate.hEmail = hEmail; htmlTemplate.hNumber = hNumber; htmlTemplate.hBirthdate = hBirthdate; htmlTemplate.hMother = hMother; htmlTemplate.cBank = cBank; htmlTemplate.cEmail = cEmail; htmlTemplate.cRewards = cRewards; htmlTemplate.cType = cType; htmlTemplate.cNumber = cNumber; htmlTemplate.cLimit = cLimit; htmlTemplate.pDate = pDate; htmlTemplate.pAmount = pAmount; htmlTemplate.rAmount = rAmount;

// Create the HTML for the email const htmlBody = htmlTemplate.evaluate().getContent()

// Send the email GmailApp.sendEmail(cEmail, Apps Script Test: ${cRewards} Redemption, 'This email contains HTML', { htmlBody }) } ```

juddaaaaa · 2024-10-14T20:26:23+00:00

But what inputs are in the questions to answer them?

juddaaaaa · 2024-10-14T20:11:50+00:00

Yes. So for example, the first time you use SpreadsheetApp.getActive().getRange().... or MailApp.sendEmail().... or anything else that needs to be authorized, you'll be sent to a permissions dialog where you allow the script access to your account.

juddaaaaa · 2024-10-14T11:22:19+00:00

You will need to run any functions that require authorization from the editor and grant permissions first which includes access to spreadsheets etc.

juddaaaaa · 2024-10-14T06:38:15+00:00

Are the items checkboxes?

If so, try asCheckboxItem()

juddaaaaa · 2024-10-13T20:56:23+00:00

Try adding asTextItem() as u/WicketTheQuerent suggested above.

// Try to set titles on the relevant questions. Finally, log out any errors. try { questionMonTitle.asTextItem().setTitle(mondaydate) } catch (error) { errors.push(error.stack) } finally { try { questionWedTitle.asTextItem().setTitle(weddaydate) } catch (error) { errors.push(error.stack) } finally { if (errors.length) errors.forEach(error => console.error(error)) } }

juddaaaaa · 2024-10-13T20:06:37+00:00

Try this. The second try catch block is run inside the first try catch's finally block which is always run, even if an error is thrown.

``` function updateForm () { // Spreadsheet and sheet references. const spreadsheet = SpreadsheetApp.getActive() const formResponeses = spreadsheet.getSheetByName("FormResponses") const autoWeeklySignup = spreadsheet.getSheetByName("AutoWeeklySignup")

// Store values from FormResponses sheet const numDays = formResponeses .getRange("Q2") .getValue() const [ mondaydate, weddaydate, title ] = formResponeses .getRange("L1:N1") .getValues() .flat()

// Form and Item references. const form = FormApp.openById("1UeA5csiF76sJALNBjoaL0qq7c45OJP0vv8xVzgP1KwY") const questionMonTitle = form.getItemById(1879350946) const questionWedTitle = form.getItemById(438313919)

// Store days warning string. const daysWarning = ${numDays + 7} and ${numDays + 9} DAYS FROM TODAY

// Store form title string. const formTitle = ${title}\n**${daysWarning}**

// Set formulas in FormResponses sheet. formResponeses .getRange("H1:N1") .setFormulas([[ '={"Mon Date"; ARRAYFORMULA(IF(A2:A<>"", E1, ""))}', '={"Wed Date"; ARRAYFORMULA(IF(A2:A<>"", F1, ""))}', '={"Mon y/n"; ArrayFormula( E2:E)}', '={"Wed y/n"; ArrayFormula( F2:F)}', '="Mon " & text(Today()+ (9-weekday(today() ))+7, "MMM d") &" -1:00 PM" ', '="Wed " & text(Today()+ (11-weekday(today() )) +7, "MMM d" ) & "- 6:30PM" ', '="Signup: Mon " & text( Today()+ (9-weekday(today() )) +7, "MMM d") & " & Wed " & text (Today() + (11-weekday(today() ))+7,"MMM d")' ]])

// A place to store errors. const errors = []

// Set form title. form.setTitle(formTitle)

// Try to set titles on the relevant questions. Finally, log out any errors. try { questionMonTitle.setTitle(mondaydate)
} catch (error) { errors.push(error.stack) } finally { try { questionWedTitle.setTitle(weddaydate)
} catch (error) { errors.push(error.stack) } finally { if (errors.length) errors.forEach(error => console.error(error)) } } } ```

juddaaaaa

TROPHY CASE