How to fix this error please?

[haz3453]

Hello All,

Apologies in advance, I am not a programmer and used CHATGPT to generate the following code for me and it is giving me this error at execution:

Error TypeError: Cannot read properties of undefined (reading 'FORMULAS') colorCells @ Code.gs:25

I have SheetA and SheetB.

SheetA has a :

Date in the 4th column,

Description in the 5th column and

Colour code in the 6th column

function colorCells() {

// Get the active sheet (Sheet A)

var sheetA = SpreadsheetApp.getActiveSheet();

// Get the data range for Sheet A

var dataRange = sheetA.getDataRange();

// Get the values in the data range as a 2D array

var values = dataRange.getValues();

// Get the active spreadsheet (which contains Sheet A and Sheet B)

var spreadsheet = SpreadsheetApp.getActive();

// Get Sheet B

var sheetB = spreadsheet.getSheetByName("SheetB");

// Loop through the rows in Sheet A

for (var i = 0; i < values.length; i++) {

// Get the date, description, and color for the current row

var date = values[i][3];

var description = values[i][4];

var color = values[i][5];

// Find the cell with the date in Sheet B

var cell = sheetB.getRange(1, 1, sheetB.getLastRow(), sheetB.getLastColumn()).find("^=" + date + "$", { lookIn: SpreadsheetApp.LookIn.FORMULAS });

// If the cell was found

if (cell) {

// Set the background color of the cell

cell.setBackground(color);

// Add the description as a note to the cell

cell.setNote(description);

}

}

}

I want the macro to find the date from SheetA in SheetB and add a note as the description and colour it with the colour provided in SheetA.

This is what GPT3 gave me:

All dates are written in mm/dd/yyyy format and are formatted as: Format >> Date and time >> custom date and time >> Day(5)

Would really appreciate your help.

Thanks!

[Tanaike]

Unfortunately, `SpreadsheetApp` has no property of `LookIn`. I think that the reason for your current issue of `Error TypeError: Cannot read properties of undefined (reading 'FORMULAS') colorCells @ Code.gs:25` is due to this. And also, Class Range has no method of `find`.

I'm worried that in the current stage, ChatGPT might not be able to still correctly create the sample scripts. When I saw several posted sample scripts on Stackoverflow, I saw some scripts including nonexistent methods were used.

Can I ask you about the detail of your expected goal? I couldn't understand your goal from your question. I apologize for this.

[haz3453]

Hi Tanaike,

Thank you for your reply.

I attach screenshots of SheetA and SheetB.

For each row in SheetA, I want the macro to find the date from SheetA (column 4) in SheetB and add a note as the description (column 5 from SheetA) and colour it with the colour provided (column 6 in SheetA).

Thanks.

[haz3453]

[Tanaike]

Thank you for replying. I would like to support you. But, I have to apologize for my poor English skill. Unfortunately, from your reply and 2 sample images, I cannot still understand your question. But I would like to try to understand it. When I could correctly understand it, I would like to think of a solution. I would be grateful if you can forgive my poor English skill.

[haz3453]

Nps at all and thank you for your reply.

Let me explain it in a different way. I want the macro to:

1) Go to the second row in SheetA

2) Go to 4th column in SheetA

3) Get the date in that cell for .e.g 6/16/2023

4) Search for the cell that 6/16/2023 is in SheetB

5) In that cell in SheetB, add a note from the 5th column of SheetA

6) In that cell in SheetB, colour it with the colour specific in the 6th column of SheetA

7) Repeat for third row in SheetA

[Tanaike]

Thank you for replying. About "4) Search for the cell that 6/16/2023 is in SheetB", I cannot understand about the logic for searching it from your showing image. Can I ask you about the detail of it?

[Michael O'Shaughnessy]

Just off the top of my head...

Change this line:

var cell = sheetB.getRange(1, 1, sheetB.getLastRow(), sheetB.getLastColumn()).find("^=" + date + "$", { lookIn: SpreadsheetApp.LookIn.FORMULAS });

to this:

var cell = sheetB.getRange(1, 1, sheetB.getLastRow(), sheetB.getLastColumn())

then add these 2 lines:

var shtBdates = cell.getValues()

console.log(shtBdates)

After you do this, you should get something in the console output.  Copy some of that and paste it in a post.  HOPEFULLY it will be a 2d array of full dates (like 10/11/2023)

Once we see what these values are we can move on to the next step.