skip to Main Content

I am new to google apps script. I have 3 sheets data (source), sheet(target), and master_rate. in the datasheet, there are around 4k rows of data. what I am doing is summarising the data sheet and displaying it in sheet 4. but the problem is there is so much data and I can’t copy all the data at once. i am only able to copy around 1k data at once, is there any way to get all the data at once using batch processing?

this is my code.

function testfn(){
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = activeSheet.getSheetByName('data');
  var lastRow = sourceSheet.getLastRow();

 // Select the source spreadsheet cells.
  var sourceColumnRange = sourceSheet.getRange('A2:BZ');
  var sourceColumnValues = sourceColumnRange.getValues();

  // Get target spreadsheet by its name..
  var targetSheet = activeSheet.getSheetByName('Sheet4');

  var column= sourceColumnValues[1];
  var col1 = [8,14,29,49]; //one of these 4 column have a amount
  var col2 = [77,13,28,47]; //one of these 4 column have a currency type (¥円, $ドル, €ユーロ) 
  var col4 = [0];
  var col5 = [5];
  var col6 = [66];
  var col7 = [9,15,30,50]; //one of these 4 column have a date value
  var col8 = [45,65]; //one of these 2 column have a value of origin
  var col9 = [51,59,61,62]; //one of these 4 column have a memo

  for(var i=0; i<lastRow-1; i++){
    var colA = '';
    var colB = '';
    var colC = '';
    var colD = '';
    var colE = '';
    var colF = '';
    var colG = '';
    var colH = '';
    var colI = '';
    var colJ = '';

    var date = '';
    var yearMonth = '';
      
      for(var j=0; j<=column.length; j++){
        //for transaction Amount
        if(col1.includes(j)) {
          if(sourceColumnValues[0,i][j]) {
            colA = sourceColumnValues[0,i][j];
          }

        }
        // for transaction  currency
        // to do get transaction rae from master_rate sheet and multiply
        if(col2.includes(j)) {
          if(sourceColumnValues[0,i][j]) {
            colB = sourceColumnValues[0,i][j];
            if(colB.trim() === '¥円'){
              colC = colA;  //if currency is ¥円 same as colA
            }
            else if(colB === '$ドル'){
              Logger.log(colA);
              colC =  colA * 120; //if currency is $ドル multiply colA by 120
              // Logger.log(eval(colA));
            }
            else if(colB === '€ユーロ'){
              colC = colA * 140; //if currency is €ユーロ multiply colA by 140
            }
            else{
              colC = 'error';

            }
          }
        }

        // for customer code
        if(col4.includes(j)) { 
          if(!colD) {
            var str = sourceColumnValues[0,i][j];
            var codeArr = str.split('_');

            var data = codeArr[codeArr.length - 1];
            var res = data.replace(/[^0-9]/g, "");
            colD = '10'+res;

          }
        }

        //for Z number
        if(col5.includes(j)) {
          if(!colE) {
            colE = sourceColumnValues[0,i][j];
            colG = '売上 ' + colE;
          }
        }
        //if colF has one of these value 株式会社クレヴィオ_g04, 有限会社ユニゲリカー_g03, 有限会社ユニゲ_g02 set value if null set '有限会社ユニゲ_g02'
        if(col6.includes(j)) {
          if(!colF) {
            colF = sourceColumnValues[0,i][j];
            if(colF == ''){
              colF = "有限会社ユニゲ_g02";

            }
          }
        }

        //for date 
        if(col7.includes(j)) {
          if(sourceColumnValues[0,i][j]) {
            colH = sourceColumnValues[0,i][j];
            var date = Utilities.formatDate(colH, "GMT", 'yyyy/MM/dd');
            var ym = Utilities.formatDate(colH, "GMT", 'MMMM, yyyy');
             var yearMonth = new Date(ym).toDateString();
          }
        }

        // for origin value
        if(col8.includes(j)) {
          if(!colI) {
            colI = sourceColumnValues[0,i][j];
            if(colI == ' '){
              colI = "国内";
            }
          }
        }

        // for memo
        if(col9.includes(j)) {
          if(!colJ) {
            colJ = sourceColumnValues[0,i][j];
          }
        }

        // Create a date object for the current date and time.
        var now = new Date();

      }
      //set value in Sheet4
      targetSheet.getRange(i+2, 1).setValue(colA);
      targetSheet.getRange(i+2, 2).setValue(colB);
      targetSheet.getRange(i+2, 3).setValue(colC);
      targetSheet.getRange(i+2, 4).setValue(colD);
      targetSheet.getRange(i+2, 5).setValue(colE);
      targetSheet.getRange(i+2, 6).setValue(colF);
      targetSheet.getRange(i+2, 7).setValue(colG);
      targetSheet.getRange(i+2, 8).setValue(date);
      targetSheet.getRange(i+2, 9).setValue(yearMonth);
      targetSheet.getRange(i+2, 10).setValue(colI);
      targetSheet.getRange(i+2, 11).setValue(colJ);
      targetSheet.getRange(i+2, 12).setValue(now);
         
  }
}

2

Answers


  1. Try changing this line var sourceColumnRange = sourceSheet.getRange('A2:BZ'); to this var sourceColumnRange = sourceSheet.getRange('A2:BZ' + sourceSheet.getLastRow());

    Login or Signup to reply.
  2. Can you clarify how many total cells are you trying to read and process? If I had to guess, you’re reaching maximum execution time due to inefficient use of for .. loop

    I’m suggesting that rather than updating the sheet using API call, try to build a formula using =QUERY that in my experience can handle a quite large amount of data. Using =ARRAYFORMULA with array literal, you can make the formula in one cell if that’s what you need.

    If for reasons you’re limited to using AppScript, try to place a well-placed timer in your script and report the time elapsed in console. That way, you can pinpoint what is exactly the bottleneck and try to work from there. Don’t forget to keep a notes of each improvements in runtime for each optimization that you’ve tried.

    Then, aside from using setValues rather than setValue as recommended by Google (which isn’t a huge refactor because you’re writing to a continuous row and column), so you calculate all the value, push it into an array, and then after the main loop is finished you can update the sheet at once.

    If that also didn’t work and you’re still hitting execution limit, try to separate your for .. loop, this may be challenging because you have to have your script called independently to avoid the per-execution limit and because Simple Trigger like onEdit(e) doesn’t get triggered when a cell is updated using API request so you can’t use that to trigger the next script. Maybe try to limit your total execution time to just under 180s and create a time-based schedule? There’s a 60 minutes total execution time per day that you have to be wary of, but if your total execution time exceeds that amount I’m suggesting that you’re rethinking your approach.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search