脚本重命名&根据表格重新排列表单 [英] Script to rename & reorder sheets based on table

问题描述

问题1

我试图编写一个脚本，根据其中一张工作表上的表格重新命名Google电子表格中的工作表。我一直在尝试不同的方法几个小时才能使它无效。 （我仍然试图获得循环）

这个脚本将放在一张表中，我将与其他人分享，他们可能会意外地重新排序或可能重命名表。

我已经包含了当前的代码和一个示例文件。

注意：表格名称将完全不同的，它是不可能按字母顺序排列的。

 函数OnOpen（）{
 
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var sheets = ss.getSheets（）; 
 
 for（var i in sheets）
 if（sheets [i] .getSheetId（）=='978626951'）{
 var sheet = ss.getSheets（）[i ]。 
休息; 
} 
 
 var sheetData = sheet.getRange（2,1,11,3）.getValues（）; （var a = 0; a< sheetData.length; a ++）{
 for（var b = 0; a< sheetData.length; b ++）{
）的
 
 
 var find = sheetData [a] [0]; Logger.log（片材[A] .getSheetName（））; $（）
 $ b $ if（find == sheets [a] .getSheetId（））{
 var temp = ss.getSheets（）[a] .activate（）; 
 ss.moveActiveSheet（sheetData [a] [2]）; 
 
 
 
 
 $ b code 
 
 
 $ b使用脚本链接到电子表格示例。 
 
 
 
问题2 
 
 
 
根据W3schools的说法，可以从循环内部增加一个循环。 
（代码形式W3）
 
 
  var i = 0; 
 var len = cars.length; （; i< len;）{
 text + = cars [i] +< br>的
; 
 i ++; 
} 
  
但是，当我尝试在Google Script中执行此操作时，调试器会挂起，必须刷新。这是不可能的谷歌脚本？ 
 
 
 
任何帮助都会大大增加。    我修改了你的代码有点和它现在似乎工作：
 
 
  function onOpen（）{
 
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var sheets = ss.getSheets（）; 
 
 for（var i in sheets）
 if（sheets [i] .getSheetId（）=='978626951'）{
 var sheet = sheets [i]; //重用var表
 break; 
} 
 
 
 //我在下面使用了注释代码，因为我需要为我的测试电子表格找到一组表格IDS;所以它不需要运行一次ID 
 //sheet.appendRow(['-']）
 // for（var x in sheets）
 // sheet.appendRow（[ sheets [x] .getSheetId（），sheets [x] .getSheetName（）]）; 
 // 
 
 
 var sheetData = sheet.getRange（2,1,11,3）.getValues（）; 
 
 for（var a = 0; a< sheetData.length; a ++）{
 var find = sheetData [a] [0]; 
 
 for（var b = 0; b  if（find == sheets [b] .getSheetId（））{
 Logger。日志（片材并[b] .getName（））; 
 
张[b] .activate（）; // not：ss.getSheets（）[a] 
 ss.moveActiveSheet（sheetData [a] [2]）; 
张[b] .setName（sheetData [a] [1]）; //我添加了这个重命名位
 
 
 
 
 $ b $ / code> 
（但是请注意，对于我来说，我甚至还需要在打开表单后刷新ss（即浏览器中的F5） - 看起来脚本对电子表格进行了更改，但它们在我刷新之前不会显示在页面上）
 
 
 
我试过这个电子表格中的代码： https://docs.google.com/spreadsheets/d/1W7IiEQgsHlWFm7EThyN3lcY6duKh9EW2sQDYFqbwID8/edit?usp=sharing   
 
 
您的Q2：
在Google Apps脚本中可以正常工作： 
 
 
  function myFunction ）{
 var cars ='cars'，text =''; 
 var i = 0; 
 var len = cars.length; （; i< len;）{
 text + = cars [i] +< br>的
; 
 i ++; 
 
 Logger.log（文本）
} 
  
 
Question 1.


I'm trying to write a script to rename and reorder sheets in a Google Spreadsheet based on a table on one of the sheets. I have been trying different methods for hours to get it to work to no avail. (I am still trying to get the hang of loops)

This script will be in a sheet that I will share with other people who may accidently reorder or possibly rename a sheet.

I have included the current code and a sample file.

NOTE: the sheet names will be completely different and it won't be possible to order them alphabetically.
function OnOpen(){

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();

 for( var i in sheets )
  if(sheets[i].getSheetId() == '978626951'){
  var sheet = ss.getSheets()[i];
   break;
 }

 var sheetData = sheet.getRange(2,1,11,3).getValues();

        for (var a = 0; a < sheetData.length; a++) {
            for (var b = 0; a < sheetData.length; b++){

    var find = sheetData[a][0]; Logger.log(sheets[a].getSheetName());                 

        if(find == sheets[a].getSheetId()) {        
    var temp = ss.getSheets()[a].activate(); 
               ss.moveActiveSheet(sheetData[a][2]);

    }  
   }  
  }
 }
Link to sample spreadsheet with script.

Question No. 2 

According to W3schools, it is possible to increment a loop from inside the loop. 
(code form W3)
var i = 0;
var len = cars.length;
for (; i < len; ) { 
    text += cars[i] + "<br>";
    i++;
} 
However, when I try to do this in Google Script the debugger hangs and I have to refresh. Is this not possible in Google script? 

Any help would be greatly appreicated.
解决方案
I modified your code a little and it seems to work now :
function onOpen(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();

  for( var i in sheets )
    if(sheets[i].getSheetId() == '978626951'){
      var sheet = sheets[i]; // reuse the var sheets
      break;
    }


  //I USED COMMENTED CODE BELOW SINCE I NEEDED TO FIND THE SET OF SHEET IDS FOR MY TEST SPREADSHEET; SO ITS NOT REQUIRED TO RUN ONCE IDs ARE FOUND
  //sheet.appendRow(['-'])
  //  for( var x in sheets)
  //    sheet.appendRow([sheets[x].getSheetId(), sheets[x].getSheetName() ] );
  //  


  var sheetData = sheet.getRange(2,1,11,3).getValues();

  for (var a = 0; a < sheetData.length; a++) {    
    var find = sheetData[a][0]; 

    for(var b = 0 ; b < sheets.length; b++){
      if(find == sheets[b].getSheetId() ) {
        Logger.log(sheets[b].getName());

        sheets[b].activate(); // not : ss.getSheets()[a] 
        ss.moveActiveSheet(sheetData[a][2]);
        sheets[b].setName(sheetData[a][1]); // I added this rename bit

      } 
    } 
  }
}
(but note that for me I even still have to refresh the ss (i.e. F5 in the browser) after having opened the sheet - it seems like the script makes the changes to the spreadsheet, but they are not shown on the page until I refresh)

I tried the code in this spreadsheet: https://docs.google.com/spreadsheets/d/1W7IiEQgsHlWFm7EThyN3lcY6duKh9EW2sQDYFqbwID8/edit?usp=sharing

Your Q2:
this works fine in Google Apps Script:
function myFunction() {
  var cars = 'cars', text = '';
  var i = 0;
  var len = cars.length;
  for (; i < len; ) { 
    text += cars[i] + "<br>";
    i++;
  } 
  Logger.log(text)
}


                        
这篇关于脚本重命名&amp;根据表格重新排列表单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！