Google App Script to find a date across multiple Sheets

0
votes

I have a Spreadsheet with many sheets inside it, Each sheet represents one month, and has dates in column B. I'm trying to create a function that searches for the current date and inserts the current time in column C. I've got a function to work by specifying the sheet, but I want it to find the sheets itself.

I've got the following code:

function insertTime() {
var date = new Date();

var hours = date.getHours()
var minutes =  date.getMinutes()
var timeStamp = hours + ':' + minutes



var ss = SpreadsheetApp.getActiveSpreadsheet()//// get the sheet
var sheets = ss.getSheets();// get all the sheets
var today = new Date(); //get todays date

for (var sheetNum = 1; sheetNum < sheets.length; sheetNum++){
 var sheet = ss.getSheets()[sheetNum]
 var columnB = sheet.getRange(2, 2, sheet.getLastRow()-1, 1); // get all the rows
 var bValues = columnB.getValues(); // get the values
 for (var i = 0; i < bValues.length; i++) { // repeat loop
var fDate = new Date(bValues[i][0]);
if (fDate.getDate() == today.getDate() && 
    fDate.getMonth() == today.getMonth() && 
    fDate.getFullYear() == today.getFullYear()) { // if the date in the cell is today's date...

   sheet.getRange(i + 2, 3, 1, 1).setValue(timeStamp);   
}
}
}
}

Which is giving me the following error: "The coordinates or dimensions of the range are invalid. (line 16)"

2
google-apps-script

2 Answers

1
votes

At least one of your sheets is empty or has only 1 row of data.

In this case on line 16, sheet.getLastRow()-1 will be 0 or -1 which is creating the error.

1
votes

I figured it out in the end, I used the forEach function:

function myFunction(){ 
var date = new Date();

var hours = date.getHours()
var minutes =  date.getMinutes()
var timeStamp = hours + ':' + minutes

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();
sheets.forEach (function (sheet) {

var columnB = sheet.getRange(2, 2, 70, 1); 
var bValues = columnB.getValues(); 
var today = new Date(); 
for (var i = 0; i < bValues.length; i++) { 
var fDate = new Date(bValues[i][0]);
if (fDate.getDate() == today.getDate() && 
    fDate.getMonth() == today.getMonth() && 
    fDate.getFullYear() == today.getFullYear()) { 
    sheet.getRange(i + 2, 7, 1, 1).setValue(timeStamp);   
}
}
}