电子表格中所有工作表上的setActiveCell. Google Apps脚本 [英] setActiveCell on all sheets within a spreadsheet. Google Apps Script

问题描述

我有一个包含多个工作表的Google电子表格.我希望将光标设置为所有选项卡的特定单元格onOpen.第一行的每个单元格代表一年中的一个星期，从这一年开始直到整个一年.我编写了一些代码，以便在打开时在第5行上预先选择该特定的列(一年中的某周).

I have a Google spreadsheet with multiple sheets. I would like the cursor to be set to a particular cell onOpen for all tabs. Each cell of the first row represents a week of the year, start to finish, for the whole year. I've written some code to have that particular column (week of the year) preselected on row 5 when opening.

当前，只有最后一张纸具有我想要的单元格.这是因为我猜setActiveCell一次只能用于一个单元格.我也试过setActiveRange和setActiveSelection无济于事.

Currently, only the last sheet has the cell selected that I want. This is because setActiveCell can only be used for one cell at a time I guess. I have also tried setActiveRange and setActiveSelection to no avail.

几年前，我发现了一个问题此处，但是解决方案对我不起作用.从那以后也许有所改变.

I found a question from years ago Here, but the solution does not work for me. Perhaps something has changed since then.

这是我的代码:

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  Date.prototype.getWeek = function() {
    var onejan = new Date(this.getFullYear(),0,1);
    return Math.ceil((((this - onejan) / 86400000) + onejan.getDay()+1)/7);
  }
  var now = new Date();
  var weekNum = now.getWeek(); 
  var sheets = ss.getSheets();
  for(var i = 0; i < sheets.length; i++) {
    var newRange = sheets[i].getRange(5,weekNum + 1);      
    sheets[i].setActiveCell(newRange);
  }
}

在上面提供的链接中，我尝试了所有给定解决方案的差异，但是我的代码从未完全相同，因为我没有在寻找与OP完全相同的东西.例如，使用Jacob Jan Tuistra的最新代码解决方案，我想到了:

Within the link I provided above, I've tried variances of all of the given solutions, but my code was never exactly the same because I wasn't looking for the exact same things as the OP. For example, using Jacob Jan Tuistra's latest code solution, I came up with:

function setCursor() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  Date.prototype.getWeek = function() {
    var onejan = new Date(this.getFullYear(),0,1);
    return Math.ceil((((this - onejan) / 86400000) + onejan.getDay()+1)/7);
  }
  var now = new Date();
  var weekNum = now.getWeek(); 
  ss.getSheets().forEach(function (sheet, weekNum) {
  Logger.log(weekNum);
      sheet.setActiveRange(sheet.getRange(5,weekNum+1));
    });
}

哪个也只设置最后一张纸

Which also only set the last sheet

推荐答案

好像您需要致电

Looks like you need to make a call to SpreadsheetApp.flush() when you loop over the sheets collection:

function onOpen() {
  SpreadsheetApp.getActive().getSheets().forEach(function (s) {
    s.setActiveSelection("B4");
    SpreadsheetApp.flush(); // Force this update to happen
  });
}

我的猜测是，Apps Script的执行引擎可以优化对单个调用的写入，并且在该单个调用中，只能进行一个活动选择.调用冲洗力每张纸要调用1次以进行更新.

My guess is Apps Script's execution engine optimizes the writes to a single call, and in that single call only one active selection can be made. Calling flush forces 1 call per sheet to be updated.

这篇关于电子表格中所有工作表上的setActiveCell. Google Apps脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！