CODEWITHSUNDEEP
google-sheetsgoogle-sheets-api
Ellone
Ellone

Reputation: 3898

Automatically change cells value at specific dates

I want to achieve following on the google spreadsheet :

Let's say I want some of my cells to take the value of another cell at specific dates and times.

The value is actually just a background color, but if that's a problem, it could be some text, even if I prefer the background color.

For example in this sheet : https://docs.google.com/spreadsheets/d/15icn5bi-wZY1HZeLwpoM2Z_XrKOqydUH1BgdDGEEqLM/edit?usp=sharing

Let's say I want my cells A3, A5 and A6 to become like the cell F1 every monday, wednesday and friday at 6:30 am GMT+1.

Then the user can modify it but every Monday, Wednesday and Friday at 6:30 am GMT+1 the value is reset to the value of F1.

Upvotes: 0

Views: 105

Answers (2)

Ed Nelson
Ed Nelson

Reputation: 10259

You can do this with this code and a time based trigger.

function colorCells() {
var ss=SpreadsheetApp.getActiveSpreadsheet()  
var s=ss.getSheetByName("Feuille 1")
var c1=s.getRange("F1").getBackground()
s.getRange("A3").setBackground(c1)
s.getRange("A5").setBackground(c1)
s.getRange("A6").setBackground(c1)
}

On the scrips menu, select Edit>Current project's triggers. Click to add a new one. Select the colorCells function, Time-driven, Week timer, Every Monday, 6pm to 7pm. Select add new trigger and repeat for Wed and Fri. You can't specify exactly the minute it will run, but it will run in the selected hour. Here is an example you can copy and try.

https://docs.google.com/spreadsheets/d/1y-YP7AckNCRZO9w_dc43ZpGbFrfZh-zBKiuC_bEiDdI/edit?usp=sharing

Upvotes: 1

Hugo do Carmo
Hugo do Carmo

Reputation: 189

I would try to accomplish this by:

  1. First, I would create a script to change the background of the desired cells using SpreadsheetApp;
  2. Second, I would create a script using Selenium WebDriver to open the spreadsheet and execute the function;
  3. Finally, schedule the selenium script to run at specific dates (cron);

Upvotes: 1

Related Questions