Question 223963 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Reduce redundant code by optimizing of appendRow() App script

I’m working on a project where I shall send data from my app to a google spreadsheet in 2 sheets through app script.

I set up a paraphrase to secure the connection. Hkey

I have a lot of data to send like computer1,brand1,cpu1,ram1,computer2,brand2,cpu2,ram2 until 20.

my first version of the code comprise a lot of data like below:

function doGet(e) {
  var ss = SpreadsheetApp.getActive();
  var sheet1 = ss.getSheetByName("Sheet1");
  var Sheet2 = ss.getSheetByName('Sheet2');

  var Region = e.parameter.Region ; 
  var Place = e.parameter.Place ;
  var Staff = e.parameter.Staff ;

  var computer1 = e.parameter.computer1;
  var brand1 = e.parameter.brand1;
  var cpu1 = e.parameter.cpu1;
  var ram1 = e.parameter.ram1;

  var computer2 = e.parameter.computer2;
  var brand2 = e.parameter.brand2;
  var cpu2 = e.parameter.cpu2;
  var ram2 = e.parameter.ram2;

  var Hkey = e.parameter.Hkey;

if (Hkey == 'AB12xyz') {
  sheet1.appendRow(Region, Place, Staff, computer, brand, cpu, ram);
  Sheet2.appendRow(Region, Place, Staff, computer, brand, cpu, ram);
  }
}

Then I optimised the code to avoid repeated tasks.
where if the computer is true, then append computer1,brand1,cpu1,ram1 and so on…until computer20,brand20,cpu20,ram20

Optimised code :

function doGet(e) {
  var ss = SpreadsheetApp.getActive();
  var sheet1 = ss.getSheetByName("Sheet1");
  var Sheet2 = ss.getSheetByName('Sheet2');

  var ID = "FS" + new Date("GMT+4", "ddMM");
  
  var Region = e.parameter.Region ; 
  var Place = e.parameter.Place ;
  var Staff = e.parameter.Staff ;
  var Shop = e.parameter.Shop;
  var Location = e.parameter.Location ;
  var Date_Checked = e.parameter.Date_Checked ;
  var Time_Checked = e.parameter.Time_Checked ;
  var Link = "check your inbox" ;
  var Remarks = e.parameter.Remarks ;

  var Hkey = e.parameter.Hkey;

  if (Hkey == 'AB12xyz') {
    for (var i = 1; i <= 20; i++) {
      var computer = e.parameter['computer' + i];
      var brand = e.parameter['brand' + i];
      var cpu = e.parameter['cpu' + i];
      var ram = e.parameter['ram' + i];

      if (computer) {
        var row = [ID,Region,Place,Staff,Shop,Location, Date_Checked,Time_Checked,Link,Remarks];
        sheet1.appendRow(row, computer, brand, cpu, ram);
        Sheet2.appendRow(row, computer, brand, cpu, ram);
       
      }
    }

The issue is that nothing is appended on the 2 sheets of the spreadsheet.
When checking the script deployment overview, I got only failures.

Tags:

2

Answers


  1. 0

    Try something like this:

    function doGet(e) {
  var ss = SpreadsheetApp.getActive();
  var sh1 = ss.getSheetByName("Sheet1");
  var sh2 = ss.getSheetByName('Sheet2');
  var ID = "FS" + new Date("GMT+4", "ddMM");
  var b = e.parameter.Region;
  var c = e.parameter.Place;
  var d = e.parameter.Staff;
  var e = e.parameter.Shop;
  var f = e.parameter.Location;
  var g = e.parameter.Date_Checked;
  var h = e.parameter.Time_Checked;
  var i = "check your inbox";
  var j = e.parameter.Remarks;
  var row = [ID, b, c, d, e, f, g, h, i, j];
  if (e.parameter.Hkey == 'AB12xyz') {
    for (var n = 1; n <= 20; n++) {
      var computer = e.parameter['computer' + n];
      var brand = e.parameter['brand' + n];
      var cpu = e.parameter['cpu' + n];
      var ram = e.parameter['ram' + n];
      let add = [computer, brand, cpu, ram];
      if (computer) {
        let r = row.concat(add);
        sh1.appendRow(r);
        sh2.appendRow(r);
      }
    }
  }
}
    Login or Signup to reply.
  2. 0

    I believe your goal is as follows.

    • You want to modify your script as a simple script.
    • You want to understand the reason for The issue is that nothing is appended on the 2 sheets of the spreadsheet. When checking the script deployment overview, I got only failures..

    Modification points:

    • In your showing script, at appendRow(row, computer, brand, cpu, ram), row is 1 dimensional array and the values of computer, brand, cpu, ram are string.

    • I think that new Date("GMT+4", "ddMM") of var ID = "FS" + new Date("GMT+4", "ddMM"); returns "Invalid Date".

    When these points are reflected in your bottom script, it becomes as follows.

    From:

    var ID = "FS" + new Date("GMT+4", "ddMM");

    To:

    var ID = "FS" + Utilities.formatDate(new Date(), "GMT+4", "ddMM");

    and

    From:

    sheet1.appendRow(row, computer, brand, cpu, ram);
Sheet2.appendRow(row, computer, brand, cpu, ram);

    To:

    sheet1.appendRow([...row, computer, brand, cpu, ram]);
Sheet2.appendRow([...row, computer, brand, cpu, ram]);

    This is the answer to your 2nd question. About your 1st question, in this case, how about the following modification?

    When appendRow is used in a loop, the process cost becomes high. Ref (Author: me)

    function doGet(e) {
  var ss = SpreadsheetApp.getActive();
  var sheet1 = ss.getSheetByName("Sheet1");
  var sheet2 = ss.getSheetByName('Sheet2');
  var ID = "FS" + Utilities.formatDate(new Date(), "GMT+4", "ddMM");
  var Link = "check your inbox";
  const { Region, Place, Staff, Shop, Location, Date_Checked, Time_Checked, Remarks, Hkey } = e.parameter;
  const row = [ID, Region, Place, Staff, Shop, Location, Date_Checked, Time_Checked, Link, Remarks];
  if (Hkey == 'AB12xyz') {
    const res = [...Array(20)].reduce((ar, _, h) => {
      var i = h + 1;
      var computer = e.parameter['computer' + i];
      if (computer) {
        ar.push([...row, computer, ...['brand', 'cpu', 'ram'].map(f => e.parameter[`${f}${i}`] || "")]);
      }
      return ar;
    }, []);
    [sheet1, sheet2].forEach(sheet => sheet.getRange(sheet.getLastRow() + 1, 1, res.length, res[0].length).setValues(res));
  }
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search