Google Sheet Script
51 views
Skip to first unread message
Matt Eichman
May 14, 2026, 8:41:45 PM
May 14
to Google Apps Script Community
Hi,
I am looking for help in writing a script for a Google Sheet that I have set up in tracking some health and exercise data in dealing with a physical condition. The Google Sheet is shared with a health provider so they can see my progress in between meetings.
I tried using scripts language suggested from AI in the Google Search, but it is not working and I do not know enough to figure out why. So, here I am seeking help from those that do. I have tried searching for answers, but have still not been able to complete it. Any help is appreciated in advance.
Here are the parameters of what i am trying to accomplish:
1. I have a single Sheet with different tabs that track different types of information. Most of them are easy enough to enter manually as there is only one table.
2. One tab is used to track multiple exercises, stretches, and movements that are rotated through to manage and improve the condition. The tab is named Movements.
3. Each item is set up in an identical table format. There are nine columns. The first row is merged with the name of the movement. The second row has the column headings of Date, Round 1, Count 1, Round 2, Count 2, Round 3, Count 3, Round 4, Count 4.
4. I named the range for each table. Taking in fact that the first row of the tab is blank and everything starts on Row 2, the range Movement1 is defined as 'Movements'!D2:L1000, then a column space, then 'Movements'!N2:V1000, then so on.
5. The tab before Movements is called Schedule. This is where I was trying to place a bound script to create entries.
6. The goal was to have an entry line on Schedule eleven columns wide. The first cell would be a dropdown list of the named ranges. Cells 2-10 would be the nine pieces of information listed in point 3. Cell 11 would be a button to press that would activate the script. The script would then add the information in a new line in the correct table referenced in the dropdown list.
Is this possible? As a bonus feature, is it possible to have a second script that recalls the most recent line entry from a table?
Hoping someone takes interest.
Thank you.
SMAARTE Group
May 14, 2026, 8:47:20 PM
May 14
to google-apps-sc...@googlegroups.com
Matt,
I copied and pasted your email into Gemini 3.1 Pro. You should get an AI subscription. It will probably save you time and money. Here's my prompt and the solution (which I encourage you to improve upon):
take a look at the email message below to a google apps script email forum and provide a one shot solution based on the description making reasonable assumptions about the sheet configuration
take a look at the email message below to a google apps script email forum and provide a one shot solution based on the description making reasonable assumptions about the sheet configuration
How to advise the user to implement this:
-
Paste the code: Go to
Extensions > Apps Script, clear out any existing code, paste the code above, and click the Save (floppy disk) icon. -
Assign the buttons: * On the
Scheduletab, insert a drawing or image to act as your "Save" button (in column K or L).-
Right-click the button, click the three vertical dots, select Assign script , and type exactly:
addMovementEntry -
Optional Bonus: Insert a second button for recalling data, and assign the script:
recallLastEntry
-
-
Permissions: The first time you click the button, Google will ask for authorization. Follow the prompts (Click Continue -> Choose your account -> Click "Advanced" -> Click "Go to script").
Why previous AI attempts likely failed the user:
Most AI tools default to sheet.getLastRow()
when appending data. If you have side-by-side tables (like D2:L1000
and N2:V1000
), getLastRow()
calculates the bottom of the deepest
table on the entire sheet. This script fixes that by pulling the values of the specific named range
and looping through its "Date" column until it finds a blank cell, isolating the tables from one another perfectly.
/** Main function to add a new movement entry from the 'Schedule' tab
* to the appropriate table on the 'Movements' tab.
* Assign this function to your "Save" button.
*/
function
addMovementEntry ()
{
var
ss =
SpreadsheetApp
.
getActiveSpreadsheet ();
var
scheduleSheet =
ss .
getSheetByName (
"Schedule"
);
// Assuming the entry line is on Row 2.
// A2 is the dropdown, B2:J2 are the 9 data points.
var
entryRange =
scheduleSheet .
getRange (
"A2:J2"
);
var
entryValues =
entryRange .
getValues ()[
0
];
var
targetRangeName =
entryValues [
0
];
// The named range from the dropdown
var
dataToLog =
entryValues .
slice (
1
);
// The 9 data points
// Validation: Check if a movement is selected
if
(!
targetRangeName ||
targetRangeName ===
""
)
{
SpreadsheetApp
.
getUi ().
alert (
"Please select a Movement from the dropdown in column A."
);
return
;
}
// Validation: Check if at least a date is entered
if
(
dataToLog [
0
]
===
""
)
{
SpreadsheetApp
.
getUi ().
alert (
"Please enter at least a Date for the entry."
);
return
;
}
// Get the target named range
var
targetRange =
ss .
getRangeByName (
targetRangeName );
if
(!
targetRange )
{
SpreadsheetApp
.
getUi ().
alert (
"Could not find the table named '"
+
targetRangeName +
"'. Check your named ranges."
);
return
;
}
// Find the next empty row within this specific named range
var
rangeValues =
targetRange .
getValues ();
var
nextEmptyRowIndex =
-
1
;
// We start the loop at index 2 (which is the 3rd row of the named range)
// because index 0 is the Title, and index 1 is the Column Headers.
for
(
var
i =
2
;
i <
rangeValues .
length ;
i ++)
{
if
(
rangeValues [
i ][
0
]
===
""
)
{
// Checking the "Date" column to see if it's empty
nextEmptyRowIndex =
i ;
break
;
}
}
if
(
nextEmptyRowIndex ===
-
1
)
{
SpreadsheetApp
.
getUi ().
alert (
"The table for "
+
targetRangeName +
" is full! Please expand the named range."
);
return
;
}
// Write the data to the correct row and columns
// targetRange.getRow() gets the absolute starting row (e.g., Row 2)
var
writeRow =
targetRange .
getRow ()
+
nextEmptyRowIndex ;
var
writeCol =
targetRange .
getColumn ();
var
writeSheet =
targetRange .
getSheet ();
writeSheet .
getRange (
writeRow ,
writeCol ,
1
,
9
).
setValues ([
dataToLog ]);
// Clear the input fields on the Schedule sheet (B2:J2), leaving the dropdown intact
scheduleSheet .
getRange (
"B2:J2"
).
clearContent ();
// Show a success message
ss .
toast (
"Entry added successfully to "
+
targetRangeName +
"!"
,
"Success"
,
3
);
}
/** Bonus function: Recalls the most recent entry from the selected table
* and populates it back into the 'Schedule' tab.
* Assign this function to a "Recall Last" button.
*/
function
recallLastEntry ()
{
var
ss =
SpreadsheetApp
.
getActiveSpreadsheet ();
var
scheduleSheet =
ss .
getSheetByName (
"Schedule"
);
// Get the selected movement from the dropdown in A2
var
targetRangeName =
scheduleSheet .
getRange (
"A2"
).
getValue ();
if
(!
targetRangeName ||
targetRangeName ===
""
)
{
SpreadsheetApp
.
getUi ().
alert (
"Please select a Movement in column A to recall."
);
return
;
}
var
targetRange =
ss .
getRangeByName (
targetRangeName );
if
(!
targetRange )
{
SpreadsheetApp
.
getUi ().
alert (
"Could not find the table named '"
+
targetRangeName +
"'."
);
return
;
}
var
rangeValues =
targetRange .
getValues ();
var
lastUsedRowIndex =
-
1
;
// Find the last row that HAS data
for
(
var
i =
2
;
i <
rangeValues .
length ;
i ++)
{
if
(
rangeValues [
i ][
0
]
===
""
)
{
lastUsedRowIndex =
i -
1
;
// The row right before the empty one
break
;
}
}
// If lastUsedRowIndex is 1, it means only the headers exist
if
(
lastUsedRowIndex <=
1
)
{
SpreadsheetApp
.
getUi ().
alert (
"There are no entries logged for "
+
targetRangeName +
" yet."
);
return
;
}
// Extract the data from the last used row
var
lastData =
rangeValues [
lastUsedRowIndex ];
// Write the data back to the Schedule entry line (B2:J2)
scheduleSheet .
getRange (
"B2:J2"
).
setValues ([
lastData ]);
ss .
toast (
"Last entry for "
+
targetRangeName +
" has been recalled."
,
"Success"
,
3
);
}
--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com .
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/35a09b1f-6b97-46f4-8c59-d5a089ed54e2n%40googlegroups.com .
Matt Eichman
May 15, 2026, 10:43:04 AM
May 15
to Google Apps Script Community
Thank you very much, this worked and helps considerably.
Reply all
Reply to author
Forward
Search
Clear search
Close search
Google apps
Main menu
