Sheets API Spreadsheets.Values.update and cross reference formulas

28 Views Asked by At

I'm developping a tool to make some quotation with :

  • a first sheet containing a database with equipment and times
  • a second sheet used for quotation (with more than 10 000 lines), the first tab contains some percentage to apply to some column of the quotation
  • an Apps Script library with the code to get the times from the database for the selected equipement

I'm using Sheets API for performance aspect.

I get the values with :

let data_chiffrage = sheetAPI.Spreadsheets.Values.get(SpreadsheetApp.getActiveSpreadsheet().getId(), rangeToGet);

then i update each cell by passing the values of data_chiffrage to a function

  for (var i=0; i< (lastRow - SETTINGS.FIRST_ROW_QUOTATION+1); i++) {    // GSheet is indexed base 1, and array is indexed base 0, starting with the first line
    updateALine_(data_chiffrage.getValues(),lastColumn, lastRow,data, indexed_col, i); // Sheets API
    data_chiffrage
  }

In the updateALine function, i update the value of a line with :

formula = formula.concat("=",SETTINGS.QTY_COLUMN_RANGE,lineIndex + parseInt(SETTINGS.FIRST_ROW_QUOTATION)," * ", data_line[k] == "" ? 0 : data_line[k].toLocaleString(lng)," * ","COEFF_DEP");
data_chiffrage[lineIndex][13] = formula;
// for example

COEFF_DEP is a named range to the cell containing the percentage to apply With a named range, no issue

If i use a cross ref range like 'Page de garde'!D26, i get an error :

formula.concat("=",SETTINGS.QTY_COLUMN_RANGE,lineIndex + parseInt(SETTINGS.FIRST_ROW_QUOTATION)," * ", data_line[k] == "" ? 0 : data_line[k].toLocaleString(lng)," * ","'Page de garde'!D26"); data_chiffrage[lineIndex][13] = formula; // for example

GoogleJsonResponseException: API call to sheets.spreadsheets.values.update failed with error : The service is currently unavailable

Any idea why an update crash when using a cross reference ?

0

There are 0 best solutions below