Can someone help me convert a script layout to actual functional code? I have general coding knowledge, but I don't know the correct syntax.
Basically what I need is a script that loops through all the sheets/tabs when a form entry is submitted. The form contains name of the person who submitted the form (string), a start date, an end date, and finally a comment field (string).
I need the script to loop through row 5 of each sheet, and look for the string entered in the first form field (name of the one who submitted the form). If it finds the name it should save the column that corresponds with that name to a variable for later use.
Then it should loop through column A (A:6-A462) to find the start and end date fields, and save both corresponding cells to variables. A new loop should loop through the cells from the start date to the end date, checking if the background color of the cells is white. If it's white it should mark them in green.
Here's a general layout of how the function can be coded;
Here's a general layout of how the function can be coded;
This is part of the solution , the methods listed are the ones you mainly need. Do have a read of the GAS documentation before posting such questions.
function obtainsheetid(){
pol = SpreadsheetApp.getActiveSpreadsheet().getSheets()[1].getRange("T1").setValue("hi")
console.log(pol)
const sheetArray = SpreadsheetApp.getActiveSpreadsheet().getSheets()
for(let i = 0 ; i < sheetArray.length;i++ ){
intValue = sheetArray[i].getRange("A5:Z5").getValues()
if(intValue.test(new RegExp('entered name'))){
sheetArray[i].getRange("A5:Z5").clearContent().setValue("entered name")
}
}
}
visit https://developers.google.com/apps-script/reference/spreadsheet/sheet for more info regarding the coding methods for GAS.