I have an AppSheet app that uses a Google sheet as the data source for its tables, although the underlying data source for the Google sheet is Salesforce. The Salesforce sObject is huge (660+ columns, hundreds of thousands of records), so I'm not able to use the AppSheet/Salesforce integration because it is very inefficient to sync all that data when I only need a tiny slice of it for each user.

I am using a Google Apps script to send a GET request to Salesforce that returns a payload of 'Workers', usually fewer than a hundred rows with 10 columns per row. The GET request selects Workers in the 'turf' of the logged-in user of the app.

The Workers table holds turf for all users, since there may be more than one user logged in at any given time, but the App only renders the turf for the current logged-in user in the UI.

There is no UI for users to modify Worker records in the app, the data is needed only to read and select Worker records in order to make updates to a related table, which writes back to Salesforce. So Salesforce always has the most updated data for Workers, and I don't need a two-way sync with the Google sheet.

The script is working but it's very slow. The whole loadTurf process takes about 30 seconds for a set of about 100 records with 10 columns per record, and I'm trying to figure out how to improve the script to make it faster. I can't control the speed of the Salesforce authentication / JWT exchange / GET request part of it, but that part only takes 3-5 seconds, and the rest of it is the processing that happens after the API payload is returned: checking for duplicates, deleting rows that need to be overwritten with fresh data, and writing the new data to the sheet.

I know there has to be a faster way to do this. The check for duplicates and delete sections seem particularly inefficient, iterating over the range one row at a time with forEach, but I couldn't figure out a way to batch-process the delete, because the turf could theoretically be in non-sequential rows. Is there a way to delete a whole filtered range in one batch without iterating over it one row at a time? Checking each row for updated columns against the Salesforce payload seemed like it would be even slower than just deleting them all and pasting in the fresh rows returned from Salesforce.

Anyway here's my code, the slowest parts seem to be the check for duplicates and delete operations, which take about 10-11 seconds each on a batch of 100 records. Can anybody suggest any ways to improve this?

const fieldsArray = [ ... list of fields ... ];
const ss = SpreadsheetApp.openByUrl( ... url ...);
const workers = ss.getSheetByName( ... sheetName ...); 
const contactIds = workers.getRange("A2:A").getValues().flat().filter(Boolean);

async function loadUserTurf(employer) {
  let records;
  if (employer) {
    try {
      const qp = new QueryParameters();
      qp.setSelect(fieldsArray.toString());
      qp.setFrom("Contact");
      qp.setWhere(`Employer_Name_Text__c = \'${employer}\' AND Active_Worker__c = TRUE`);

      records = await get(qp);
      setUserTurf(employer, records);
    } catch (err) {
      logErrorFunctions('loadUserTurf', employer, records, err);
    }

  } else {
    console.log(`loadUserTurf > 24: no employer provided`);
  }
  
}

const confirmUniqueContactId = (id) => !contactIds.includes(id);

function appendNewRows(data, sheet) { // data = array of objects
    try {
      data.forEach(obj => {
        if (confirmUniqueContactId(obj.Id)) {
          // flatten object to array
          const row = Object.values(obj).slice(1);
          sheet.appendRow(row);
        }        
      })
    } catch (err) {
      logErrorFunctions('appendNewRows', [data, sheet], '', err);
    }
}

function setUserTurf(employerName, payload) {
  // Check for matching rows -- has this turf been pulled before?
  // create an array of all matching row indices so we can delete them
  // and replace them with fresh data from Salesforce

  const allData = workers.getDataRange().getValues();
  const turfIndices = allData.map((row, index) => {
    if (row[3] === employerName) {
      return index + 1;
    }
  }).filter(n => n); // remove null values
  
  // If no matching rows found (user's first login)
  // create new rows to append the payload from Salesforce
  if (!turfIndices.length) {
    // append new rows with data from payload from loadTurf function
    try {
      appendNewRows(payload, workers);    
    } catch (err) {
      console.log(err);
      logErrorFunctions('setUserTurf', turfIndices, '', err);
    }
  } else {
    // otherwise, delete all existing rows in that turf and replace them with fresh data from Salesforce
    // because checking for differences at the individual cell level seems even more inefficient?
    try {
      turfIndices.forEach(index => workers.deleteRow(index));
      // append new rows with data from payload from loadTurf function
      appendNewRows(payload, workers); 
    } catch (err) {
      logErrorFunctions('setUserTurf', turfIndices, '', err);
    }
  }
}

I have an AppSheet app that uses a Google sheet as the data source for its tables, although the underlying data source for the Google sheet is Salesforce. The Salesforce sObject is huge (660+ columns, hundreds of thousands of records), so I'm not able to use the AppSheet/Salesforce integration because it is very inefficient to sync all that data when I only need a tiny slice of it for each user.

I am using a Google Apps script to send a GET request to Salesforce that returns a payload of 'Workers', usually fewer than a hundred rows with 10 columns per row. The GET request selects Workers in the 'turf' of the logged-in user of the app.

The Workers table holds turf for all users, since there may be more than one user logged in at any given time, but the App only renders the turf for the current logged-in user in the UI.

There is no UI for users to modify Worker records in the app, the data is needed only to read and select Worker records in order to make updates to a related table, which writes back to Salesforce. So Salesforce always has the most updated data for Workers, and I don't need a two-way sync with the Google sheet.

The script is working but it's very slow. The whole loadTurf process takes about 30 seconds for a set of about 100 records with 10 columns per record, and I'm trying to figure out how to improve the script to make it faster. I can't control the speed of the Salesforce authentication / JWT exchange / GET request part of it, but that part only takes 3-5 seconds, and the rest of it is the processing that happens after the API payload is returned: checking for duplicates, deleting rows that need to be overwritten with fresh data, and writing the new data to the sheet.

I know there has to be a faster way to do this. The check for duplicates and delete sections seem particularly inefficient, iterating over the range one row at a time with forEach, but I couldn't figure out a way to batch-process the delete, because the turf could theoretically be in non-sequential rows. Is there a way to delete a whole filtered range in one batch without iterating over it one row at a time? Checking each row for updated columns against the Salesforce payload seemed like it would be even slower than just deleting them all and pasting in the fresh rows returned from Salesforce.

Anyway here's my code, the slowest parts seem to be the check for duplicates and delete operations, which take about 10-11 seconds each on a batch of 100 records. Can anybody suggest any ways to improve this?

const fieldsArray = [ ... list of fields ... ];
const ss = SpreadsheetApp.openByUrl( ... url ...);
const workers = ss.getSheetByName( ... sheetName ...); 
const contactIds = workers.getRange("A2:A").getValues().flat().filter(Boolean);

async function loadUserTurf(employer) {
  let records;
  if (employer) {
    try {
      const qp = new QueryParameters();
      qp.setSelect(fieldsArray.toString());
      qp.setFrom("Contact");
      qp.setWhere(`Employer_Name_Text__c = \'${employer}\' AND Active_Worker__c = TRUE`);

      records = await get(qp);
      setUserTurf(employer, records);
    } catch (err) {
      logErrorFunctions('loadUserTurf', employer, records, err);
    }

  } else {
    console.log(`loadUserTurf > 24: no employer provided`);
  }
  
}

const confirmUniqueContactId = (id) => !contactIds.includes(id);

function appendNewRows(data, sheet) { // data = array of objects
    try {
      data.forEach(obj => {
        if (confirmUniqueContactId(obj.Id)) {
          // flatten object to array
          const row = Object.values(obj).slice(1);
          sheet.appendRow(row);
        }        
      })
    } catch (err) {
      logErrorFunctions('appendNewRows', [data, sheet], '', err);
    }
}

function setUserTurf(employerName, payload) {
  // Check for matching rows -- has this turf been pulled before?
  // create an array of all matching row indices so we can delete them
  // and replace them with fresh data from Salesforce

  const allData = workers.getDataRange().getValues();
  const turfIndices = allData.map((row, index) => {
    if (row[3] === employerName) {
      return index + 1;
    }
  }).filter(n => n); // remove null values
  
  // If no matching rows found (user's first login)
  // create new rows to append the payload from Salesforce
  if (!turfIndices.length) {
    // append new rows with data from payload from loadTurf function
    try {
      appendNewRows(payload, workers);    
    } catch (err) {
      console.log(err);
      logErrorFunctions('setUserTurf', turfIndices, '', err);
    }
  } else {
    // otherwise, delete all existing rows in that turf and replace them with fresh data from Salesforce
    // because checking for differences at the individual cell level seems even more inefficient?
    try {
      turfIndices.forEach(index => workers.deleteRow(index));
      // append new rows with data from payload from loadTurf function
      appendNewRows(payload, workers); 
    } catch (err) {
      logErrorFunctions('setUserTurf', turfIndices, '', err);
    }
  }
}

• google-apps-script
• salesforce
• google-appsheet

1 Answer 1

Modification points:

I thought that the following modification might be closer to your goal in your script.

• Use setValues instead of appendRow to put values into the spreadsheet.
• In order to delete multiple rows with low process cost, how about using the Sheets API?
• Also, in the case of deleting rows, I thought that in your script, the array turfIndices is required to be reversed.

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

Modified script:

First, please enable Sheets API at Advanced Google services.

And, please modify your functions appendNewRows and setUserTurf as follows.

function appendNewRows(data, sheet) {
  try {
    const values = data.reduce((ar, obj) => {
      if (confirmUniqueContactId(obj.Id)) {
        const row = Object.values(obj).slice(1);
        ar.push(row);
      }
      return ar;
    }, []);
    sheet.getRange(sheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);
  } catch (err) {
    logErrorFunctions('appendNewRows', [data, sheet], '', err);
  }
}

function setUserTurf(employerName, payload) {
  const allData = workers.getDataRange().getValues();
  const turfIndices = allData.map((row, index) => {
    if (row[3] === employerName) {
      return index + 1;
    }
  }).filter(n => n);
  if (!turfIndices.length) {
    try {
      appendNewRows(payload, workers);
    } catch (err) {
      console.log(err);
      logErrorFunctions('setUserTurf', turfIndices, '', err);
    }
  } else {
    try {
      const sheetId = workers.getSheetId();
      const requests = turfIndices.reverse().map(e => ({ deleteDimension: { range: { sheetId, startIndex: e - 1, endIndex: e, dimension: "ROWS" } } }));
      Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
      SpreadsheetApp.flush();
      appendNewRows(payload, workers);
    } catch (err) {
      logErrorFunctions('setUserTurf', turfIndices, '', err);
    }
  }
}

Note:

If you cannot use the Sheets API, please modify the above script as follows.

From

const sheetId = workers.getSheetId();
const requests = turfIndices.reverse().map(e => ({ deleteDimension: { range: { sheetId, startIndex: e - 1, endIndex: e, dimension: "ROWS" } } }));
Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
SpreadsheetApp.flush();

To

turfIndices.reverse().forEach(index => workers.deleteRow(index));

Additional information:

In your script, the functions appendNewRows and setUserTurf might be able to be merged as follows.

function setUserTurf(employerName , payload) {
  const allData = workers.getDataRange().getValues();
  const turfIndices = allData.map((row, index) => {
    if (row[3] === employerName) {
      return index + 1;
    }
  }).filter(n => n);
  if (turfIndices.length) {
    const sheetId = workers.getSheetId();
    const requests = turfIndices.reverse().map(e => ({ deleteDimension: { range: { sheetId, startIndex: e - 1, endIndex: e, dimension: "ROWS" } } }));
    Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
    SpreadsheetApp.flush();
  }
  const values = payload.reduce((ar, obj) => {
    if (!contactIds.includes(obj.Id)) {
      const row = Object.values(obj).slice(1);
      ar.push(row);
    }
    return ar;
  }, []);
  if (values.length == 0) return;
  workers.getRange(workers.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);
}

References:

• reverse()
• Method: spreadsheets.batchUpdate
• DeleteDimensionRequest