CODEWITHSUNDEEP
javascriptperformancegoogle-sheetsgoogle-apps-scriptgoogle-sheets-api
Kristina
Kristina

Reputation: 1

Why after adding 30+ codes into App Script all codes in it started to work with bugs

I'm trying to organize my partner management using Google Sheets. I have a main spreadsheet where columns F-I are dynamic, meaning I want data from these columns to be transferred to columns A-D in the partner spreadsheet.

However, this should meet three conditions:

This is my code. It was working well until I had 30 spreadsheets. After that, I noticed some bugs, and when I added 50 more spreadsheets, it almost stopped working. However, I need it to handle 300+ spreadsheets, and I want to fix it.

I've tried merging them all into one unified code and it didn't help.

function Code() {
    var sourceSpreadsheetId = 'XXX';
    var sourceSheetName = 'Sheet1';
    var destinationSpreadsheetId = 'YYY';
    var destinationSheetName = 'Sheet2';

    // Open the source spreadsheet and get the source sheet
    var sourceSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetId);
    var sourceSheet = sourceSpreadsheet.getSheetByName(sourceSheetName);
    var lastRow = sourceSheet.getLastRow();
    var sourceRange = sourceSheet.getRange('F2:I' + lastRow);
    var sourceData = sourceRange.getValues();

    // Filter rows that contain 'example.com' in column G and are marked as 'Need to be done'
    var filteredData = sourceData.filter(row => row[1].includes('example.com') && row[3] === 'Need to be done');

    // Open the destination spreadsheet and get the destination sheet
    var destinationSpreadsheet = SpreadsheetApp.openById(destinationSpreadsheetId);
    var destinationSheet = destinationSpreadsheet.getSheetByName(destinationSheetName);

    // Append the filtered data to the destination sheet
    if (filteredData.length > 0) {
        var lastRowDestination = destinationSheet.getLastRow();
        // Prevent appending if the last row is empty and it's the first run
        var startRowDestination = lastRowDestination > 0 ? lastRowDestination + 1 : 1;
        destinationSheet.getRange(startRowDestination, 1, filteredData.length, 4).setValues(filteredData);
    }

    // Update the status from 'Need to be done' to 'In progress' in the source sheet for the rows that were transferred
    filteredData.forEach((row) => {
        var rowIndex = sourceData.indexOf(row) + 2; // Calculate the index in the source sheet
        sourceSheet.getRange('I' + rowIndex).setValue('In progress');
    });

    // Apply all pending Spreadsheet changes
    SpreadsheetApp.flush();
}

Upvotes: 0

Views: 67

Answers (2)

Cooper
Cooper

Reputation: 64100

Setup up a data sheet like this:

srcid srcsht destid dstsht

code:

function Code() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("processdatasheetname");
  const vs = sh.getRange(2, 1, sh.getLastRow() - 1, 4).getValues();
  vs.forEach(([srcid, srcshnm, dstid, dstshtnm], i) => {
    let src = SpreadsheetApp.openById(srcid);
    let srcsh = src.getSheetById(srcshnm);
    let srcvs = srcsh.getRange("F2:I" + srcsh.getLastRow()).getvs().filter(([f, g, h, i]) => ~g.toString().indexOf("example.com") && i == "Need to be done").filter(e => e);
    let dss = SpreadsheetApp.openById(dstid);
    let dstsh = dss.getSheetByName(dstshtnm);
    dstsh.getRange(dstsh.getLastRow() + 1, 1, srcvs.length,srcvs[0].length).setValues(srcvs);
  });
}

If you don't need the flush the don't use it because it just takes more time. If you are not writing to the the same spreadsheet then I don't think it's an issue.

Upvotes: 1

mplungjan
mplungjan

Reputation: 178375

  1. Max 6 minutes for one process
  2. There are limits on number of API calls
  3. Your code can be more efficient - for example update in batch
function Code() {
  var sourceSpreadsheetId = 'XXX';
  var sourceSheetName = 'Sheet1';
  var destinationSpreadsheetId = 'YYY';
  var destinationSheetName = 'Sheet2';

  var sourceSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetId);
  var sourceSheet = sourceSpreadsheet.getSheetByName(sourceSheetName);
  var lastRow = sourceSheet.getLastRow();
  var sourceData = sourceSheet.getRange('F2:I' + lastRow).getValues();

  var filteredData = [];
  var rowsToUpdate = [];

  // Filter data and collect row indices
  for (var i = 0; i < sourceData.length; i++) {
    if (sourceData[i][1].includes('example.com') && sourceData[i][3] === 'Need to be done') {
      filteredData.push(sourceData[i]);
      rowsToUpdate.push(i + 2);
    }
  }

  if (filteredData.length > 0) {
    var destinationSpreadsheet = SpreadsheetApp.openById(destinationSpreadsheetId);
    var destinationSheet = destinationSpreadsheet.getSheetByName(destinationSheetName);
    var lastRowDestination = destinationSheet.getLastRow();
    var startRowDestination = lastRowDestination > 0 ? lastRowDestination + 1 : 1;

    // Append filtered data in a batch
    destinationSheet.getRange(startRowDestination, 1, filteredData.length, 4).setValues(filteredData);

    // Batch update status to 'In progress'
    var updateRange = rowsToUpdate.map(() => ['In progress']);
    sourceSheet.getRange(
      Math.min(...rowsToUpdate), 9, rowsToUpdate.length, 1
    ).setValues(updateRange);
  }

  SpreadsheetApp.flush();
}

Upvotes: 1

Related Questions