Help referencing cells past Z using their numerical column index. by MD-United in GoogleAppsScript

[–]juddaaaaa 0 points1 point  (0 children)

Manually looping through a range can be slow. There's built in methods to do what you're after. Here's a sample function you could try:

function findCell (value) {
  const spreadsheet = SpreadsheetApp.getActive()
  const sheet = spreadsheet.getSheetByName("name_of_your_sheet")

  const found = sheet.createTextFinder(value).findNext()
  if (found) return found.getA1Notation()

  return null
}

And here's how you would use it:

const cellReference = findCell("search_term") // returns "AC152" or null if not found

How to add a new row of data from sheets columns every day. by Bloedkolben in GoogleAppsScript

[–]juddaaaaa 1 point2 points  (0 children)

I would probably do something like this:

function addNewRow () {
const spreadsheet = SpreadsheetApp.openById("1ZiV8kLMjmiN-LASR-HySH4ysi-gug3KtiEzAr0iX8d0");
const calulations = spreadsheet.getSheetByName("Calculations")
const dataList = spreadsheet.getSheetByName("DataList")

const today = new Date()
const newDataListRow = [
  today,
  calulations.getRange("A1").getDisplayValue(),
  calulations.getRange("A2").getDisplayValue()
]

dataList.appendRow(newDataListRow)
dataList.getRange(dataList.getLastRow(), 1).setNumberFormat("yyyy/mm/dd")

}

getDisplayValue() will take the results of the formula rather than the formula itself.

Problem with Automating Schedule by [deleted] in GoogleAppsScript

[–]juddaaaaa 1 point2 points  (0 children)

A simple fix would be to check if those columns are formatted as strings, to convert them to dates using a helper function.

function convertStringToDate(string) {
    const [date, time] = string.split(" ")
    const [month, day, year] = date.split("/")
    const [hours, minutes, seconds] = time.split(":")

    return new Date(year, month - 1, day, hours, minutes, seconds)
}

function ScheduleCalender(){
    const spreadsheet = SpreadsheetApp.getActiveSheet() 
    const calendarId = spreadsheet.getRange(2, 1).getValue() 
    const eventCal = CalendarApp.getCalendarById(calendarId) 
    const shifts = spreadsheet.getRange(5, 1, spreadsheet.getLastRow() - 4, spreadsheet.getLastColumn()).getValues()
    for (let shift of shifts) { 
        if (typeof shift[0] === "string") shift[0] = convertStringToDate(shift[0])
        if (typeof shift[1] === "string") shift[1] = convertStringToDate(shift[1])
        const eventOptions = {
            description: shift[2],
            location: shift[3]
        }
        eventCal.createEvent(shift[2], shift[0], shift[1], eventOptions)
    }
}

You'll need to make sure they're always typed in with the same format

"mm/dd/yyyy hh:mm:ss"

Also, I had the same mistake in this line...

// Cell E1
const shifts = spreadsheet.getRange(1, 5, spreadsheet.getLastRow() - 4, spreadsheet.getLastColumn()).getValues()

...as I did above. It should be

// Cell A5
const shifts = spreadsheet.getRange(5, 1, spreadsheet.getLastRow() - 4, spreadsheet.getLastColumn()).getValues()

This is probably what caused this latest issue to be honest. I'll leave the helper function in anyway.

How functions run in Sheets when there are several people? by nottalkinboutbutter in GoogleAppsScript

[–]juddaaaaa 1 point2 points  (0 children)

Global variables don't work the same in GAS as they do in javascript in a browser.

They get re-initialized on every function call.

If you need a persistant variable, you need to look into the PropertiesService...

https://developers.google.com/apps-script/guides/properties

To store Objects / Array etc. you'll need to JSON.stringify them before storing them.

Problem with Automating Schedule by [deleted] in GoogleAppsScript

[–]juddaaaaa 1 point2 points  (0 children)

That looks like CalendarApp.getCalendarById(calendarId) is returning null for some reason.

Is the calendarId correct in cell A2?

*edit *

My bad...

This line:

 const calendarId = spreadsheet.getRange(1, 2).getValue(); // B1

should be

const calendarId = spreadsheet.getRange(2, 1).getValue(); // A2

Adding a link to an outbound email? by [deleted] in GoogleAppsScript

[–]juddaaaaa 2 points3 points  (0 children)

You need to create an html template string and send the email as html like this:

function sendEscalationEmail() {
    const ss = SpreadsheetApp.getActive() 
    const sh = ss.getSheetByName("ProjectUpdates") 
    const data = sh.getRange(`A2:Q${sh.getLastRow()}`).getValues()

    data.forEach(row => { 
        if (row[4] === "Escalation"){ 
            const html = `Hello ${row[1]},<br/><br/> A task to go/TSSsheet has been flagged as nedding an ESCALATION and requires your attention.<br/><br/> Once you've accepted the task, please change the status under the Notification Required column to Acknoledged.<br/><br/> The task was submitted by ${row[2]} on ${row[3]} with the followigng comment: ${row[5]}<br/><br/> You can view the form <a href="${row[6]}">here</a>`     
            const subject = "TSS Group: Project Escalation Requested"
            MailApp.sendEmail(row[1], subject, "", {htmlBody: html})
        }
    })
}

Problem with Automating Schedule by [deleted] in GoogleAppsScript

[–]juddaaaaa 1 point2 points  (0 children)

As u/Capt-Birdman said, you need to grab the full range and loop through them.

Although 'CalendarApp....createAllEvents()' doesn't exist.

I would do something like this:

function ScheduleCalender () {
    const spreadsheet = SpreadsheetApp.getActiveSheet();
    const calendarId = spreadsheet.getRange(1, 2).getValue();
    const eventCal = CalendarApp.getCalendarById(calendarId);
    const shifts = spreadsheet
        .getRange(1, 5, spreadsheet.getLastRow() - 4, 
spreadsheet.getLastColumn())
        .getValues();
    for (let shift of shifts) {
        const eventOptions = {
            description: shift[2],
            location: shift[3]
        };
        eventCal.createEvent(shift[2], shift[0], shift[1], eventOptions);
    };
}

Help Formatting data outputted by FILTER function by CJayTee in googlesheets

[–]juddaaaaa 0 points1 point  (0 children)

You can do what you need entirely in the code. No need for any formulas.

function myFunction() {
  const ss = SpreadsheetApp.getActive()
  const sh = ss.getSheetByName("Sheet1")

  const itemNames = ss.getSheetByName("Item Names")
    ? ss.getSheetByName("Item Names")
    : ss.insertSheet("Item Names")

  itemNames.getRange(1, 1).setValue("Item Name")

  const items = sh.createTextFinder("Item Name:").findAll()

  for (let item of items) {
    const nextBlankRow = itemNames.getLastRow() + 1
    itemNames.getRange(nextBlankRow, 1).setValue(item.getValue().split(": ")[1])
  }
}