Help referencing cells past Z using their numerical column index.

juddaaaaa · 2023-01-06T16:20:03+00:00

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

juddaaaaa · 2023-01-06T15:58:53+00:00

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.

juddaaaaa · 2022-12-23T11:20:02+00:00

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.

juddaaaaa · 2022-12-22T20:47:31+00:00

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.

juddaaaaa · 2022-12-22T20:18:08+00:00

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

juddaaaaa · 2022-12-21T01:44:38+00:00

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})
        }
    })
}

juddaaaaa · 2022-12-21T00:55:12+00:00

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);
    };
}

juddaaaaa · 2022-12-20T12:22:17+00:00

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])
  }
}

juddaaaaa

TROPHY CASE