API call to sheets.spreadsheets.values.append failed with error: Empty response

Question

I am using Google Sheet API Request and I am not getting even an error message to know what I am lacking or what I am doing wrong. I am getting API call to sheets.spreadsheets.values.append failed with error: Empty response

So I tried reading the docs here. Weirdly enough, when I have approximately less than 2500 rows of data in my sheet, the api works, but when I increase it to more than 2500 (note that: I do not know the exact row threshold of this behavior but its between 2000-2500), the api call just doesn't do anything and return me an Empty Response.

setValues() is out of my choice as Google Sheets' API Requests is faster and is better for my use case.

My use case is I need to copy and paste formulas from my first row up to my last row. In order to do that, I loop creating an array of new formulas for each row and then store it in an array (that makes it a 2d array), and then that is what my input values to the API Request.

See code snippet below:

  try{
    let lastRow = sheet.getLastRow();
    let formulaRow = headers + 1; 
    let formula = getNewFormulaRange(formulaRange, formulaRow);

    let sourceFormulaRange = sheet.getRange(formula);
    const sourceFormulas = sourceFormulaRange.getFormulas()[0]; // Extract just the first (and only) row of formulas

    let formulasToBeApplied = [];

    for (let i = formulaRow+1; i <= lastRow; i++) {
        // For each original formula, modify it to suit the new row number
        const adjustedFormulas = sourceFormulas.map(formula => 
            formula.replace(new RegExp(`(\\$?[A-Z]+)\\$?${formulaRow}`, 'g'), (match, cellReference) => {
                // Replace all instances of column references followed by 'formulaRow' with the same column reference followed by the new row number
                return `${cellReference}${i}`;
            })
        );
        formulasToBeApplied.push(adjustedFormulas);
    }

    const startingAppendRow = getNewFormulaRange( formulaRange, headers+2 );
    const valueRange = Sheets.newValueRange();
    valueRange.values = formulasToBeApplied;
    const options = {
      valueInputOption: "USER_ENTERED"
    }

    let result;
    if (formulasToBeApplied.length > 0) {
      result = Sheets.Spreadsheets.Values.append(valueRange, sheetId, `${sheet.getName()}!${startingAppendRow}`, options);
    }

Although, unfortunately, I do not know your actual situation, I proposed a modification point as an answer. Please confirm it. If I misunderstood your actual situation and that was not useful, I apologize. — Tanaike
– Tanaike, Commented Apr 16, 2024 at 4:56

Tanaike · Accepted Answer · 2024-04-16 04:56:46Z

Unfortunately, from your question, I do not know your actual situation. But, in my experience, when an error Empty response occurs with Sheets API, the inputted values are large. Ref (Author: me) If this situation is the same with your situation, how about the following modification?

From:

if (formulasToBeApplied.length > 0) {
  result = Sheets.Spreadsheets.Values.append(valueRange, sheetId, `${sheet.getName()}!${startingAppendRow}`, options);
}

To:

if (formulasToBeApplied.length > 0) {
  const split = 2000;
  const v = [...Array(Math.ceil(formulasToBeApplied.length / split))].map((_) => formulasToBeApplied.splice(0, split));
  result = v.map(values => Sheets.Spreadsheets.Values.append({ values }, sheetId, `${sheet.getName()}!${startingAppendRow}`, options));
}

In this modification, the values of formulasToBeApplied are appended to the sheet by splitting the values.
From note that: I do not know the exact row threshold of this behavior but its between 2000-2500, the number of rows is 2000. But, if an error occurs, please adjust the value of const split = 2000; like const split = 1000; and test it again.

Collectives™ on Stack Overflow

API call to sheets.spreadsheets.values.append failed with error: Empty response

1 Answer 1

From:

To:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

From:

To:

Comments

Your Answer

Sign up or log in

Post as a guest

Related