`googlesheets` add `upsertValues` function

[mtuchi]

Description

In some cases you want to update values in a matched row or create a new row. The hardest part is figuring out the range for a matched row that you want to update. A function like upsertRowValues or upsertValues can be really useful and make job code look simple and clean.

Suggestions

⚠️ The suggested implementation 👇🏽 , needs lots of improvements

/**
 * Updates values in a Spreadsheet.
 * @function
 * @param {Object} params - Data object to add to the spreadsheet.
 * @param {string} [params.spreadsheetId] The spreadsheet ID.
 * @param {string} [params.range] The range of values to update.
 * @param {string} [params.searchValue] The unique value to search in each row to find row index.
 * @param {string} [params.columnName] The column that will be used to find a unique identifier.
 * @param {string} [params.valueInputOption] (Optional) Value update options. Defaults to 'USER_ENTERED'
 * @param {array} [params.values] A 2d array of values to update.
 * @param {function} callback - (Optional) callback function
 * @returns {Operation} spreadsheet information
 */
export function upsertValues(params, callback = s => s) {
  return async state => {
    const [resolvedParams] = expandReferences(state, params);

    const addNumberToRange = (rangeString, numberToAdd) => {
      // Parse the sheet name and range
      const [sheetName, range] = rangeString.split('!');
      const [startColumn, endColumn] = range.split(':');

      // Construct the updated range
      const updatedRange = `${sheetName}!${startColumn + numberToAdd}:${
        endColumn + numberToAdd
      }`;

      return updatedRange;
    };

    const {
      spreadsheetId,
      range,
      valueInputOption = 'RAW',
      valuesToUpdate,
      columnName,
      searchValue,
    } = resolvedParams;

    try {
      const response = await client.spreadsheets.values.get({
        spreadsheetId,
        range,
      });

      const values = response.data.values;
      const uniqueIdentifierColumnIndex = values[0].indexOf(columnName);

      if (uniqueIdentifierColumnIndex === -1) {
        throw new Error('Column not found: ' + columnName);
      }

      // Find the row index where the unique identifier matches
      const rowIndex = values.findIndex(
        row => row[uniqueIdentifierColumnIndex] === searchValue
      );

      if (rowIndex !== -1) {
        // If a match is found, update the values in the corresponding row
        const updateRange = addNumberToRange(range, rowIndex);
        const updateResponse = await client.spreadsheets.values.update({
          spreadsheetId,
          range: updateRange,
          valueInputOption,
          resource: {
            values: valuesToUpdate,
          },
        });
        console.log('Updated value:', updateResponse.data);
        return callback({
          ...composeNextState(state, updateResponse.data),
          response: updateResponse,
        });
      } else {
        // If no match is found, create a new row with the search value and the new values
        const newRowRange = addRangeNumber(range, values.length + 1);
        const updateResponse = await client.spreadsheets.values.update({
          spreadsheetId,
          range: newRowRange,
          valueInputOption,
          resource: {
            values: valuesToUpdate,
          },
        });
        console.log('Inserted new row:', updateResponse.data);
        console.log('%d cells updated.');
        return callback({
          ...composeNextState(state, updateResponse.data),
          response: updateResponse,
        });
      }
    } catch (err) {
      logError(err);
      throw err;
    }
  };
}

[mtuchi]

The upsertValues function. Will remove the following anti-pattern

getValues(
  '18129',
  'mtuchi-testing!A:AD',
  state => {
    const {
      taskId,
      data: { values },
    } = state;

    const rowIndex = values.findIndex(row => row['0'] === taskId);

    if (rowIndex !== -1) {
      state.updateRange = `mtuchi-testing!A${rowIndex + 1}:AD${rowIndex + 1}`;
    }

    return state;
  }
);

fn(state => {
  if (state.updateRange) {
    console.log('Updating row', state.updateRange);
    return batchUpdateValues({
      spreadsheetId: '18129',
      range: state.updateRange,
      values: state.gsheetValues,
    })(state);
  }

  console.log('Adding new row');
  return appendValues({
    spreadsheetId: '18129',
    range: 'mtuchi-testing!A2:AD2',
    values: state.gsheetValues,
  })(state);
});

[aleksa-krolls]

@mtuchi I know you came up with the appscript option... so what would next steps be for upsertValues()?

If there is still work to do, do you think Hunter could pick this up and finish it?

[mtuchi]

@aleksa-krolls i think upsertValues() is the hard problem to solve. And might need Joe's input on designing the function

[aleksa-krolls]

@mtuchi ok then will add to backlog. Lower priority rn.

[josephjclark]

Hi @mtuchi

I probably need to spend a bit more time with the adaptor before I weigh in on this. Maybe I can do that tomorrow.

I am worried that if you add this, the API as a whole gets a bit confusing? My gut feeling is that its hard to understand for new users.

The questions I'm asking myself are:

• Why do we want append, batchUpdate ANDupsert`? That's three functions doing every similar things in a small API.
• Are the upsert semantics clear? Presumably it's means appendOrUpdate? Like append to the end of the range if it doesn't exist, or update the row if it does.
• Does the function have too many inputs?

I guess I'm just worried that it's overly specific.

What if you restructured your job example something like this? One of the two operations will always be a no-op (it won't do anything)

getValues(
  '18129',
  'mtuchi-testing!A:AD',
  state => {
    const {
      taskId,
      data: { values },
    } = state;

    const rowIndex = values.findIndex(row => row['0'] === taskId);

    if (rowIndex !== -1) {
       state.updateRange = `mtuchi-testing!A${rowIndex + 1}:AD${rowIndex + 1}`;
       state.toAppend = state.gsheetValues;
       state.toUpsert = [];
    } else {
       state.updateRange =  'mtuchi-testing!A2:AD2';
       state.toAppend= [];
       state.toUpsert= state.gsheetValues;
   }

    return state;
  }
);

batchUpdateValues({
      spreadsheetId: '18129',
      range: $.updateRange,
      values: $.toUpdate,
 })

appendValues({
  spreadsheetId: '18129',
      range: $.updateRange,
  values: $.toAppend
})

[josephjclark]

Maybe another approach is just to expose the gsheets client directly? A bit like we do with get. It's kinda crude but looking at the adaptor, all it does is proxy to the client anyway

useSheets((state, sheetsClient) => {
    // do whatever you want with the client
    const response = await sheetsClient.values.batchUpdate({
        spreadsheetId: 'abc',
        resource:  { data: [{ range, values }]
     });

  // return a new state object
   return { ...state, data: response.data }
})

[josephjclark]

Here's the plan after a phone call with @mtuchi:

• We will leave the job code as it is, anti-pattern and all. I think we can re-write it better but I don't really think it's worth it.
• I am not sold on upsertValues (conceptually messy, doesn't map to the google API directly, bloats the API). Let's wait and see if anyone else needs it
• I've raised an issue for exposing the client directly and we should seriously consider ithttps://github.com/OpenFn/adaptors/issues/575
• I've also raised an issue which would optimise my little job re-write https://github.com/OpenFn/adaptors/issues/576
• We've noted that fnIf() would help us here so I'm linking this as evidence in support of #375

[mtuchi]

@aleksa-krolls See joe's feedback above ☝🏽