Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
372 views
in Technique[技术] by (71.8m points)

Nightly Script that Inserts values into different sheet based on date - Google Sheets

I have a sheet "Sheet1" where a user inputs data in cells A3:E3. The goal is to then move that data into "Sheet2" where column A4:A34 is a numeric value for the day of the month. I want to move the data inputted on "Sheet1" into "Sheet2" on the row that corresponds with the current day of the month. Then "Sheet1" would clear itself for the next day ahead.

enter image description here

I'll be honest I'm not great with functions and I'm mainly looking for help on where to look to find a solution. I've tried different code that I do know how to use like Query but the issue is it doesn't lock the value in place and as soon as the values are wiped in Sheet1 they are then removed via the query.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

You will need to run a function on a time-driven trigger. The function can use Range.getValues() to read the data, get the current date's day number with const dayNumber = new Date().getDate(), look up the day number in Sheet2 with Range.getValues().flat().indexOf(dayNumber), write the data with Range.setValues() and finally clear the source range with Range.clearContent().

It is unclear what the purpose the day numbers in Sheet2 serve, because the data would in any case be written once a day. It would seem simpler to always append the most recent data at the first available row and include a timestamp.

For an example of how to do this, see the appendRowsToArchiveSheet script.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...