如何使Google表格脚本在Samsung平板电脑上可靠运行? [英] How can I make a Google Sheet script run reliably on Samsung tablets?

问题描述

我的Google表格脚本中有两个功能，每个功能都通过一个复选框触发(因为移动版Google表格无法将图片用作按钮).它们可以在PC上运行(速度较慢)，但是在平板电脑上，它们的失败往往会更多，而这也会影响PC用户.

I have two functions in my Google Sheet script which are each triggered by means of a checkbox (since Google Sheets on mobile can't use images as buttons). They work on PC (rather slowly), but on tablets, they tend to fail more often than not, which then also affects PC users.

该脚本设置为对两个复选框单元格执行onEdit检查.如果选中了单元格C3中的复选框，则应运行AUTOFILL函数(该功能将显示信息表上最后一行的A单元格值加上数据条目表的单元格C4中的1，然后清除该复选框)，并且选中单元格C12中的复选框，应运行SUBMIT函数(该函数将接受在数据输入"表上输入的数据范围，并使用数据输入表中的信息更新数据表上的现有行/在数据表上添加新行，并添加一个如果数据输入页上的单元格C11包含单词"CLEANED"，然后清除该复选框，则为时间戳记.)

The script is set up to perform an onEdit check of two checkbox cells. If the checkbox in cell C3 is checked, the AUTOFILL function should run (which displays the A cell value of the last row on the Info sheet plus 1 in cell C4 of the Data Entry sheet, and then clears the checkbox), and if the checkbox in cell C12 is checked, the SUBMIT function should run (which takes the range of data entered on the Data Entry sheet and updates an existing row/adds a new row on the Data sheet with the information from the Data Entry sheet, adding a timestamp if cell C11 on the Data Entry sheet contains the word 'CLEANED', and then clears the checkbox).

我曾尝试过各种WIFI信号强度和功能更强大的平板电脑，但我无法在此处指出确切的罪魁祸首-有时它会运行，大多数情况下，该复选框只会保持选中状态，什么也不会发生.便携式计算机和台式计算机似乎都可以运行，但是如果平板电脑尝试运行并发生故障，则计算机有时也不会运行，直到我进入脚本本身并手动强制运行一次功能，这似乎可以重置并让计算机再次工作.

I've tried experimenting with various WIFI signal strengths and more powerful tablets, but I am unable to pinpoint the exact culprit here - sometimes this will run, most often the checkbox will just remain checked and nothing happens. Laptops and desktop computers all seem to run, but if a tablet tries to run and fails, the computers will sometimes not run either, until I go into the script itself and manually force once of the functions to run, which seems to reset things and lets the computers work again.

是因为运行此代码所需的处理?我已经尝试过尽可能优化它，但是在这里我还有可能要改变的其他地方使每次工作都起作用吗?

Is it because of the processing required in order to run this code? I've tried to optimize it as much as possible, but is there something else that I might change here which would make this work, every time?

此处是示例表这是脚本:

function onEdit(e) {
   if (e.range.getSheet().getName() != "Data Entry") {
       return
   }

  var isAutofill = SpreadsheetApp.getActiveSheet().getRange("C3").getValue();
  var isSubmit = SpreadsheetApp.getActiveSheet().getRange("C12").getValue();

    if (isAutofill && isSubmit) {
    Browser.msgBox("You cannot autofill and submit data at the same time!");
    SpreadsheetApp.getActiveSheet().getRange("C3").setValue(false);
    SpreadsheetApp.getActiveSheet().getRange("C12").setValue(false);
  } else if (isAutofill) {
    AUTOFILL();
    SpreadsheetApp.getActiveSheet().getRange("C3").setValue(false);
  } else if (isSubmit) {
    SUBMIT();
    SpreadsheetApp.getActiveSheet().getRange("C12").setValue(false);
  }
}


function AUTOFILL() {
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Info');
  var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data Entry');
  var valueOfData = sheet1.getRange(sheet1.getLastRow(), 1).getValue();
  sheet2.getRange('C4').setValue(valueOfData + 1);
} 


function SUBMIT() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var formSS = ss.getSheetByName("Data Entry");
var dataSheet = ss.getSheetByName("Info");

var values = formSS.getRange("C4:C11").getValues().reduce(function(a, b) {
    return a.concat(b)
});
var partNum = values[0];
var row;
dataSheet.getDataRange().getValues().forEach(function(r, i) {
    if (r[0] === partNum) {
        row = i + 1
    }
})
row = row ? row : dataSheet.getLastRow() + 1;
var data = dataSheet.getRange(row, 1, 1, 8).getValues()[0].map(function (el, ind){
  return el = values[ind] ? values[ind] : el;
  })

var statusValue = formSS.getRange("C11").getValue();

if (statusValue != 'CLEANED') {
dataSheet.getRange(row, 1, 1, 8).setValues([data]);
}

if (statusValue == 'CLEANED') {
var now = [new Date()];
var newData =  data.concat(now)
dataSheet.getRange(row, 1, 1, 9).setValues([newData]);
}

formSS.getRange("C4:C11").clearContent()
}

推荐答案

我做了一些更改.看一看.希望您可以使用它们来加快功能.

I made a few changes. Take a look. Hopefully you can use them to speed up the function.

function onEdit(e) {
  var sh=e.range.getSheet();
  if (sh.getName() != "Data Entry") {return;}
  var rgC3=SpreadsheetApp.getActiveSheet().getRange("C3");
  var rgC12=SpreadsheetApp.getActiveSheet().getRange("C12");
  var isAutofill = rgC3.getValue();
  var isSubmit = rgC12.getValue();

  if (isAutofill && isSubmit) {
    e.source.toast("You cannot autofill and submit data at the same time!");
    rgC3.setValue(false);
    rgC12.setValue(false);
  } else if (isAutofill) {
    AUTOFILL(e.source);
    rgC3.setValue(false);
  } else if (isSubmit) {
    SUBMIT(e.source);
    rgC12.setValue(false);
  }
}

function AUTOFILL(ss) {
  var sheet1 = ss.getSheetByName('Info');
  var sheet2 = ss.getSheetByName('Data Entry');
  var valueOfData = sheet1.getRange(sheet1.getLastRow(), 1).getValue();
  sheet2.getRange('C4').setValue(valueOfData + 1);
} 

function SUBMIT(ss) {
var formSS=ss.getSheetByName("Data Entry");
var dataSheet=ss.getSheetByName("Info");
var values=formSS.getRange("C4:C11").getValues().reduce(function(a, b) {return a.concat(b)});
var partNum = values[0];
var row;
var data=dataSheet.getDataRange().getValues()
for(var i=0;i<data.length;i++) {
  if(data[i][0]==partNum) {
    row=i+1;
    break;
  }
}
row = row ? row : dataSheet.getLastRow() + 1;
var data = dataSheet.getRange(row, 1, 1, 8).getValues()[0].map(function (el, ind){return el = values[ind] ? values[ind] : el;})
var statusValue = formSS.getRange("C11").getValue();
if (statusValue != 'CLEANED') {dataSheet.getRange(row, 1, 1, 8).setValues([data]);}
if (statusValue == 'CLEANED') {var now = [new Date()];var newData=data.concat(now);dataSheet.getRange(row, 1, 1, 9).setValues([newData]);}
formSS.getRange("C4:C11").clearContent();
}

这篇关于如何使Google表格脚本在Samsung平板电脑上可靠运行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！